# -*- coding: utf-8 -*-
from __future__ import unicode_literals
# from django.http import HttpResponse
from django.shortcuts import render
from django.shortcuts import redirect
from django.shortcuts import HttpResponse
from basic import models as basic_models
from django_celery_beat import models as beat_models
from cron import models as cron_models
from django.db.models import Q, QuerySet
from django.core.paginator import Paginator
import cx_Oracle
import os
import json
import time
from django.http import JsonResponse
import ast
import paramiko
import re
from datetime import datetime, date
global curr_time
from dateutil.parser import parse

# global curr_time2
curr_time = time.strftime('%Y年%m月%d日 %H:%M:%S', time.localtime(time.time()))

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
# Create your views here.

# def login(request):
#     error_msg = ""
#     if request.method == 'POST':
#         user = request.POST.get('user',None)
#         pwd = request.POST.get('pwd',None)
#         login_obj = models.user_info.objects.filter(username=user,password=pwd).first()
#         if login_obj:
#             return redirect('/oratk/index.html')
#             #return HttpResponse("登入成功")
#         else:
#             error_msg = "用户名或密码错误"
#             #print request.path
#             return render(request, 'login.html', {'error_msg': error_msg})
#     if request.method == 'GET':
#         return render(request,'login.html',{'error_msg':error_msg})

"""def user_add(request):
    ##创建数据
    models.user_info.objects.create(username='test',password='test')
    ##查询 result 是对象列表 queryset类型
    ##查询所有
    #result = models.user_info.objects.all()
    ##带条件查询
    result = models.user_info.objects.filter(username='root')
    for row in result:
        print(row.username,row.password)
    ##删除
    #models.user_info.objects.filter(username='root').delete()
    ##更新
    models.user_info.objects.all().update(password='123')
"""


# def index(request):
#     if request.method == 'POST':
#         pass
#     if request.method == 'GET':
#         return render(request,'index.html')

def oratk_select(request):
    pass


### ora工具查询页 ####
def oratk_select_new(request):
    global select_name
    global select_conn_string
    global columns
    global result
    global dbname
    global conn_string
    if request.method == 'POST':
        ##获取要查询的数据库连接串
        # select_conn_string.split()[1] 为对应的连接串
        select_name = request.POST.get('tool_name', None)
        select_conn_string = request.POST.get('conn_string', None)
        submit_id = request.POST.get('submit_id', 0)
        arg_count = request.POST.get('arg_count', 0)
        dbname = select_conn_string.split('=')[0]
        conn_string = select_conn_string.split('=')[1]
        # print (select_name)
        # print (select_conn_string)
        ##获取查询项目对应的sql
        ##sql.sqltext 为对应的sql
        sql = basic_models.oratk_tool_info.objects.filter(name=select_name).first()
        ##数据库连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()
        sqltxt = sql.sqltext
        ##执行sql
        # cursor.execute(sql.sqltext)
        if arg_count == '1':
            print('参数个数为：1')
            arg1 = request.POST.get('arg1', None)
            print(arg1)
            # sqltxt = sqltxt.encode('utf-8').replace(':1',arg1)
            sqltxt = sqltxt.replace(':1', arg1)
            print(sqltxt)
        if arg_count == '2':
            print('参数个数为：2')
            arg1 = request.POST.get('arg1', None)
            arg2 = request.POST.get('arg2', None)
            print(arg1, arg2)
            # sqltxt = sqltxt.encode('utf-8').replace(':1',arg1).replace(':2',arg2)
            sqltxt = sqltxt.replace(':1', arg1).replace(':2', arg2)

            print(sqltxt)
        cursor.execute(sqltxt)
        ##列名
        columns = [col[0] for col in cursor.description]
        # print (columns)
        ##一条记录为一个字典,结果集为列表
        # cursor.rowfactory = lambda *args: dict(zip(columns, args))
        ##获取结果
        result = cursor.fetchall()
        print(result)
        if submit_id == 0:
            return render(request, 'oratk_app/oratk_select_use.html', {'select_name': select_name,
                                                                       'dbname': dbname,
                                                                       'select_conn_string': conn_string,
                                                                       'columns': columns,
                                                                       'result': result})
        else:
            return render(request, 'oratk_app/oratk_table.html', {'select_name': select_name,
                                                                  'dbname': dbname,
                                                                  'select_conn_string': conn_string,
                                                                  'columns': columns,
                                                                  'result': result})
    if request.method == 'GET':
        return render(request, 'oratk_app/oratk_select_use.html', {'select_name': select_name,
                                                                   'select_conn_string': select_conn_string,
                                                                   'columns': columns,
                                                                   'result': result})


def oratk_select_ora_bak(request):
    global select_name
    global select_conn_string
    global columns
    global result
    global dbname
    global conn_string
    global arg1
    global arg2
    global arg3
    if request.method == 'POST':
        ##获取查询项
        ##根据查询项获取sqltext
        select_name = request.POST.get('tool_name', None)
        sql = cron_models.ora_point_info.objects.filter(name=select_name).first()
        sqltxt = sql.sqltext
        ##获取根绝name=args获取参数列表
        argslist = request.POST.getlist('args', None)
        args_count = len(argslist)
        if args_count == '1':
            arg1 = argslist[0]
            sqltxt = sqltxt.replace(':1', arg1)
        if args_count == '2':
            arg1 = argslist[0]
            arg2 = argslist[1]
            sqltxt = sqltxt.replace(':1', arg1).replace(':2', arg2)
        if args_count == '3':
            arg1 = argslist[0]
            arg2 = argslist[1]
            arg3 = argslist[2]
            sqltxt = sqltxt.replace(':1', arg1).replace(':2', arg2).replace(':3', arg3)
        ##获取数据库连接串
        select_conn_string = request.POST.get('conn_string', None)
        ##获取数据库名字
        dbname = select_conn_string.split('=')[0]
        ##获取数据库连接串
        conn_string = select_conn_string.split('=')[1]
        ##db连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()
        ##获取sql执行方式
        exec_type = request.POST.get('exec_type', None)
        ##获取提交方式
        submit_id = request.POST.get('submit_id', 0)
        ##submit_id=0 为ajax提交方式的标记
        if submit_id == '0':
            if exec_type == 'sql执行':
                # 执行sql
                cursor.execute(sqltxt)
                ##列名
                columns = [col[0] for col in cursor.description]
                # print (columns)
                ##一条记录为一个字典,结果集为列表
                # cursor.rowfactory = lambda *args: dict(zip(columns, args))
                ##获取结果
                result = cursor.fetchall()
                result1 = cursor.fetchall()
                return render(request, 'oratk_app/oratk_table_ora.html', {'select_name': select_name,
                                                                          'dbname': dbname,
                                                                          'select_conn_string': conn_string,
                                                                          'columns': columns,
                                                                          'result': result})
            if exec_type == 'shell执行':
                pass
        ##form post提交方式
        else:
            if exec_type == 'sql执行':
                # 执行sql
                cursor.execute(sqltxt)
                ##列名
                columns = [col[0] for col in cursor.description]
                # print (columns)
                ##一条记录为一个字典,结果集为列表
                # cursor.rowfactory = lambda *args: dict(zip(columns, args))
                ##获取结果
                result = cursor.fetchall()
                return render(request, 'oratk_app/oratk_select_use.html', {'select_name': select_name,
                                                                           'dbname': dbname,
                                                                           'select_conn_string': conn_string,
                                                                           'columns': columns,
                                                                           'result': result})

            if exec_type == 'shell执行':
                pass
    if request.method == 'GET':
        return render(request, 'oratk_app/oratk_select_use.html', {'select_name': select_name,
                                                                   'select_conn_string': select_conn_string,
                                                                   'columns': columns,
                                                                   'result': result})


##ora工具查询页 ajax和form提交方式
def oratk_select_ora(request):
    global select_name
    global select_conn_string
    global columns
    global result
    global dbname
    global conn_string
    global arg1
    global arg2
    global arg3
    arg1 = ''
    arg2 = ''
    arg3 = ''
    if request.method == 'POST':

        # 定义request 的参数列表
        args_dic = {}

        ##获取数据库连接串
        select_conn_string = request.POST.get('conn_string', None)
        ##获取数据库名字
        dbname = select_conn_string.split('=')[0]
        ##获取数据库连接串
        conn_string = select_conn_string.split('=')[1]
        ##db连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()

        ##获取提交方式
        submit_id = request.POST.get('submit_id', 1)
        if submit_id == '0':
            args_list = request.POST.get('args', 0)
            argslist = ast.literal_eval(args_list)
        else:
            ##获取根绝name=args获取参数列表
            argslist = request.POST.getlist('args', None)
        print(argslist)
        args_count = len(argslist)
        # print(argslist[0])
        if args_count == 1:
            arg1 = argslist[0]
            # sqltxt = sqltxt.replace(':1', arg1)
        if args_count == 2:
            arg1 = argslist[0]
            arg2 = argslist[1]
            # sqltxt = sqltxt.replace(':1', arg1).replace(':2', arg2)
        if args_count == 3:
            arg1 = argslist[0]
            arg2 = argslist[1]
            arg3 = argslist[2]
            # sqltxt = sqltxt.replace(':1', arg1).replace(':2', arg2).replace(':3', arg3)

        print(arg1)
        ##获取查询项
        ##根据查询项获取sqltext
        select_name = request.POST.get('tool_name', None)
        sql = cron_models.ora_point_info.objects.filter(name=select_name).first()
        sqltextlist = ast.literal_eval(sql.sqltext.replace('\r', '').replace('\n', ''))

        args_dic['select_name'] = select_name
        args_dic['dbname'] = dbname
        args_dic['select_conn_string'] = select_conn_string
        args_dic['conn_string'] = conn_string
        arc_c = 0
        try:
            for i in sqltextlist:
                args_dic['desc' + str(arc_c)] = i[0] + ':'
                # 替换sql
                sqltext = i[1].replace(':1', arg1).replace(':2', arg2).replace(':3', arg3)
                print(sqltext)
                cursor.execute(sqltext)
                ##列名
                args_dic['columns' + str(arc_c)] = [col[0] for col in cursor.description]
                ##一条记录为一个字典,结果集为列表
                # cursor.rowfactory = lambda *args: dict(zip(columns, args))
                ##获取结果
                # result = cursor.fetchall()
                args_dic['result' + str(arc_c)] = cursor.fetchall()

                arc_c = arc_c + 1
                print(arc_c)

            ##获取sql执行方式
            exec_type = request.POST.get('exec_type', None)

            ##submit_id=0 为ajax提交方式的标记
            if submit_id == '0':
                if exec_type == 'sql执行':
                    print(args_dic)
                    return render(request, 'oratk_app/oratk_table_ora.html', args_dic)
                if exec_type == 'shell执行':
                    pass
            ##form post提交方式
            else:
                if exec_type == 'sql执行':
                    return render(request, 'oratk_app/oratk_select_use_ora.html', args_dic)
                if exec_type == 'shell执行':
                    pass
        except Exception as err:
            return HttpResponse("<span style='color:red'>ERR:  %s </splan>" % (err))
    if request.method == 'GET':
        return render(request, 'oratk_app/oratk_select_use.html', {'select_name': select_name,
                                                                   'select_conn_string': select_conn_string,
                                                                   'columns': columns,
                                                                   'result': result})


### ora工具查询页  ####
def oratk_select_ora_args_ajax(request):
    if request.method == 'POST':
        ##获取ora_point name值
        point_name = request.POST.get('tool_name', None)
        point_info = cron_models.ora_point_info.objects.filter(name=point_name).first()
        # 是否有参数
        args_mk = point_info.args_mark
        # 执行方式
        exec_type = point_info.exec_type
        exec_type_list = ast.literal_eval(exec_type)
        if args_mk == '否':
            return render(request, 'oratk_app/oratk_ora_args_table_2.html', {'exec_type_list': exec_type_list})
        if args_mk == '是':
            ##参数值
            args_val = point_info.args_val
            args_val_list = args_val.split(';')
            return render(request, 'oratk_app/oratk_ora_args_table.html',
                          {'args_val_list': args_val_list, 'exec_type_list': exec_type_list})


##分页方法
def v_paginator(data, current_page_num, line_count_set):
    # 以下是分页
    # 5条一页
    # 总页数：paginator.num_pages
    # 当前页：current_page_num
    # 当前页数据：current_page
    # 总行数：paginator.count
    global paginator
    global current_page
    global page_range
    # paginator = Paginator(data, 5)  # 对所有数据进行分页
    paginator = Paginator(data, line_count_set)
    global last_page
    last_page = paginator.num_pages

    try:  # 捕捉前台传过来的数据，传过来不正常的数据都跳到第一页
        global current_page
        current_page = paginator.page(current_page_num)  # 拿哪一页
        if paginator.num_pages > 11:  # 判断总页数是否大于 10 页
            if current_page_num - 5 < 1:  # 页数小于前5页就显示前10页
                current_range = range(1, 11)
                page_range = current_range
                return current_range, current_page, paginator, page_range, last_page
            elif current_page_num + 5 > paginator.num_pages:  # 页数大于最后5页就显示最后10页
                current_range = range(paginator.num_pages - 10, paginator.num_pages + 1)
                page_range = current_range
                return current_range, current_page, paginator, page_range, last_page
            else:
                current_range = range(current_page_num - 4, current_page_num + 4)  # 其他范围为-5页到+5页
                page_range = current_range
                return current_range, current_page, paginator, page_range, last_page
        else:
            page_range = paginator.page_range  # 小于10页就显示所有页数
            return page_range, current_page, paginator, page_range, last_page

    except Exception as e:
        current_page_num = 1  # 随便乱传取第一页
        current_page = paginator.page(current_page_num)  # 随便乱传则取第一页
        current_range = range(1, 12)
        return paginator, current_page, current_range, page_range, last_page


##功能使用初始页
def oratk_use(request):
    if request.method == 'POST':
        ##获取要查询的数据库连接串
        # select_conn_string.split()[1] 为对应的连接串
        select_name = request.POST.get('tool_name', None)
        select_conn_string = request.POST.get('conn_string', None)
        dbname = select_conn_string.split('=')[0]
        conn_string = select_conn_string.split('=')[1]
        # print dbname
        # print (select_conn_string)
        ##获取查询项目对应的sql
        ##sql.sqltext 为对应的sql
        sql = basic_models.oratk_tool_info.objects.filter(name=select_name).first()
        ##数据库连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()
        ##执行sql
        cursor.execute(sql.sqltext)
        ##列名
        columns = [col[0] for col in cursor.description]
        # print (columns)
        ##一条记录为一个字典,结果集为列表
        # cursor.rowfactory = lambda *args: dict(zip(columns, args))
        ##获取结果
        result = cursor.fetchall()
        print(result)
        # for res in row:
        #     print (res)
        #     for k,v in res.items():
        #         print (k)
        #         print (v)
        return render(request, 'oratk_app/oratk_select_use.html', {'select_name': select_name,
                                                                   'dbname': dbname,
                                                                   'select_conn_string': conn_string,
                                                                   'columns': columns,
                                                                   'result': result})
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='locked'))
            oratk_tool_info = basic_models.oratk_tool_info.objects.all()
            return render(request, 'oratk_app/oratk_use.html',
                          {'instanceinfo_result': instanceinfo_result, 'oratk_tool_info': oratk_tool_info})


##ora功能使用
def oratk_use_ora(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='locked')).order_by('dbname')
            oratk_tool_info = cron_models.ora_point_info.objects.all().order_by('name')
            print(type)
            return render(request, 'oratk_app/oratk_use_ora.html',
                          {'instanceinfo_result': instanceinfo_result, 'oratk_tool_info': oratk_tool_info})


##oratk 自定义查询
def oratk_query(request):
    if request.method == 'POST':
        ##获取要查询的数据库连接串
        # select_conn_string.split()[1] 为对应的连接串
        sqltext = request.POST.get('sqltext', None)
        select_conn_string = request.POST.get('conn_string', None)
        submit_id = request.POST.get('submit_id', 0)

        dbname = select_conn_string.split('=')[0]
        conn_string = select_conn_string.split('=')[1]
        sql = sqltext
        # 如果有分号去掉分号
        if sql.rstrip()[-1] == ';':
            sql = sql.rstrip()[:-1]

        print(select_conn_string.split('=')[1])
        print(sql)
        ##数据库连接

        # select_conn_string.split()[1] 为对应的连接串
        try:
            db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
            cursor = db.cursor()
            ##执行sql
            cursor.execute(sql)
            ##列名
            columns = [col[0] for col in cursor.description]
            # print (columns)
            ##一条记录为一个字典,结果集为列表
            # cursor.rowfactory = lambda *args: dict(zip(columns, args))

            ##获取结果
            result = cursor.fetchall()
            print(result)
            count_limt = 500

            if submit_id == 0:
                if len(result) >= count_limt:
                    return HttpResponse('超出显示限制,显示的条目数不超过500')
                else:
                    return render(request, 'oratk_app/oratk_select_query.html', {'dbname': dbname,
                                                                                 'select_conn_string': conn_string,
                                                                                 'columns': columns,
                                                                                 'result': result})
            else:
                if len(result) >= count_limt:
                    return HttpResponse('超出显示限制,显示的条目数不超过500')
                else:
                    return render(request, 'oratk_app/oratk_table.html', {'dbname': dbname,
                                                                          'select_conn_string': conn_string,
                                                                          'columns': columns,
                                                                          'result': result})

        except cx_Oracle.DatabaseError as msg:
            print('sql_exec_except:')
            error, = msg.args
            # 获取sql执行失败时的错误信息  #err cx_oracle 返回的报错
            err_msg = error.message
            respone = 'sql执行异常,报错信息为(sql_exec_except):  ' + err_msg
            return HttpResponse(respone)
        except Exception as err:
            return HttpResponse(err)

    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='locked')).order_by('dbname')
            return render(request, 'oratk_app/oratk_query.html', {'instanceinfo_result': instanceinfo_result})


def oratk_use_ajax(request):
    if request.method == 'POST':
        res = {'col': '', 'data': ''}
        select_conn_string = request.POST.get('v_dbname', None)
        select_name = request.POST.get('v_name', None)
        # 获取查询项目对应的sql
        ##sql.sqltext 为对应的sql
        sql = basic_models.oratk_tool_info.objects.filter(name=select_name).first()
        ##数据库连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()
        ##执行sql
        cursor.execute(sql.sqltext)
        ##列名
        columns = [col[0] for col in cursor.description]
        # print (columns)
        ##一条记录为一个字典,结果集为列表
        # cursor.rowfactory = lambda *args: dict(zip(columns, args))
        ##获取结果
        result = cursor.fetchall()
        res['col'] = columns
        res['data'] = result
        print(res)
        print(json.dumps(res))
        return HttpResponse(json.dumps(res))


### 功能点增加页 ###
def oratk_tool_info(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            ##当用户搜索时：
            if user_search != 'all':
                oratk_tool_info_result = basic_models.oratk_tool_info.objects.filter(Q(name__contains=user_search)
                                                                                     | Q(sqltext__contains=user_search)
                                                                                     | Q(status__contains=user_search)
                                                                                     | Q(remake__contains=user_search))
                v_paginator(oratk_tool_info_result, currentpage_num, line_count_set)
            # 当用户没有搜索时：
            else:
                user_search = ''
                oratk_tool_info_result = basic_models.oratk_tool_info.objects.all()
                v_paginator(oratk_tool_info_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_tool_info.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')


##ora工具功能项管理
def oratk_tool_info_ora(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            ##当用户搜索时：
            if user_search != 'all':
                oratk_tool_info_result = cron_models.ora_point_info.objects.filter(Q(name__contains=user_search)
                                                                                   | Q(sqltext__contains=user_search)
                                                                                   | Q(status__contains=user_search)
                                                                                   | Q(remake__contains=user_search))
                v_paginator(oratk_tool_info_result, currentpage_num, line_count_set)
            # 当用户没有搜索时：
            else:
                user_search = ''
                oratk_tool_info_result = cron_models.ora_point_info.objects.all()
                v_paginator(oratk_tool_info_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_tool_info_ora.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')


### 添加功能点 ###
def oratk_tool_add(request):
    if request.method == 'POST':
        add_name = request.POST.get('add_name', None)
        add_sqltext = request.POST.get('add_sqltext', None)
        add_status = request.POST.get('add_status', None)
        add_remake = request.POST.get('add_remake', None)
        basic_models.oratk_tool_info.objects.create(name=add_name, sqltext=add_sqltext, status=add_status,
                                                    remake=add_remake)
        return redirect("/oratk_app/oratk_tool_info")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/oratk_tool_add.html')
        else:
            return redirect('/basic_app/login.html')


## 编辑功能点
def user_edit(request):
    if request.method == 'POST':
        v_id = request.POST.get('v_id', None)
        add_name = request.POST.get('add_name', None)
        add_sqltext = request.POST.get('add_sqltext', None)
        add_status = request.POST.get('add_status', None)
        add_remake = request.POST.get('add_remake', None)
        basic_models.oratk_tool_info.objects.filter(id=v_id).update(name=add_name, sqltext=add_sqltext,
                                                                    status=add_status, remake=add_remake)
        return redirect("/oratk_app/oratk_tool_info")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            edit_result = basic_models.oratk_tool_info.objects.get(id=nid)
            return render(request, 'oratk_app/oratk_tool_edit.html', {'oratk_tool_info_result': edit_result})
        else:
            return redirect('/basic_app/login.html')


##功能删除
def oratk_tool_delete(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            basic_models.oratk_tool_info.objects.filter(id=nid).delete()
            return redirect("/oratk_app/oratk_tool_info")
        else:
            return redirect('/basic_app/login.html')


##功能锁定
def oratk_tool_lock(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            basic_models.oratk_tool_info.objects.filter(id=nid).update(status='locked')
            return redirect("/oratk_app/oratk_tool_info")
        else:
            return redirect('/basic_app/login.html')


##功能详情页
def oratk_tool_more(request):
    if request.method == 'POST':
        return redirect("/oratk_app/oratk_tool_info")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            more_result = basic_models.oratk_tool_info.objects.get(id=nid)
            return render(request, 'oratk_app/oratk_tool_more.html', {'more_result': more_result})
        else:
            return redirect('/basic_app/login.html')


### 添加功能点 ###
def oratk_tool_add_ora(request):
    if request.method == 'POST':
        add_name = request.POST.get('add_name', None)
        add_sqltext = request.POST.get('add_sqltext', None)

        add_desc = request.POST.get('add_desc', None)
        add_args_mark = request.POST.get('add_args_mark', None)
        add_args_count = request.POST.get('add_args_count', None)
        add_args_type = request.POST.get('add_args_type', None)
        add_args_status = request.POST.get('add_args_status', None)
        add_args_val = request.POST.get('add_args_val', None)
        add_exec_type = request.POST.getlist('add_exec_type', None)
        add_status = request.POST.get('add_status', None)
        add_remake = request.POST.get('add_remake', None)
        cron_models.ora_point_info.objects.create(name=add_name, sqltext=add_sqltext, desc=add_desc,
                                                  args_mark=add_args_mark, args_count=add_args_count,
                                                  args_type=add_args_type, args_status=add_args_status,
                                                  args_val=add_args_val, exec_type=add_exec_type, status=add_status,
                                                  remake=add_remake)
        return redirect("/oratk_app/oratk_tool_info_ora")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/oratk_tool_add_ora.html')
        else:
            return redirect('/basic_app/login.html')


## 编辑功能点
def user_edit_ora(request):
    if request.method == 'POST':
        v_id = request.POST.get('v_id', None)
        add_name = request.POST.get('add_name', None)
        add_sqltext = request.POST.get('add_sqltext', None)
        print(add_sqltext)
        add_desc = request.POST.get('add_desc', None)
        add_args_mark = request.POST.get('add_args_mark', None)
        add_args_count = request.POST.get('add_args_count', None)
        add_args_type = request.POST.get('add_args_type', None)
        add_args_status = request.POST.get('add_args_status', None)
        add_args_val = request.POST.get('add_args_val', None)
        add_exec_type = request.POST.getlist('add_exec_type', None)
        add_status = request.POST.get('add_status', None)
        add_remake = request.POST.get('add_remake', None)
        cron_models.ora_point_info.objects.filter(id=v_id).update(name=add_name, sqltext=add_sqltext, desc=add_desc,
                                                                  args_mark=add_args_mark, args_count=add_args_count,
                                                                  args_type=add_args_type, args_status=add_args_status,
                                                                  args_val=add_args_val, exec_type=add_exec_type,
                                                                  status=add_status,
                                                                  remake=add_remake)
        return redirect("/oratk_app/oratk_tool_info_ora")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            edit_result = cron_models.ora_point_info.objects.get(id=nid)
            return render(request, 'oratk_app/oratk_tool_edit_ora.html', {'oratk_tool_info_result': edit_result})
        else:
            return redirect('/basic_app/login.html')


##功能删除
def oratk_tool_delete_ora(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            cron_models.ora_point_info.objects.filter(id=nid).delete()
            return redirect("/oratk_app/oratk_tool_info_ora")
        else:
            return redirect('/basic_app/login.html')


##功能锁定
def oratk_tool_lock_ora(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            cron_models.ora_point_info.objects.filter(id=nid).update(status='locked')
            return redirect("/oratk_app/oratk_tool_info_ora")
        else:
            return redirect('/basic_app/login.html')


##功能详情页
def oratk_tool_more_ora(request):
    if request.method == 'POST':
        return redirect("/oratk_app/oratk_tool_info_ora")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            more_result = cron_models.ora_point_info.objects.get(id=nid)
            return render(request, 'oratk_app/oratk_tool_more_ora.html', {'more_result': more_result})
        else:
            return redirect('/basic_app/login.html')


###ora工具shell
##ora工具shell执行
def oratk_use_ora_shell(request):
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='locked'))
            return render(request, 'oratk_app/oratk_use_ora_shell.html',
                          {'instanceinfo_result': instanceinfo_result})


##ora工具脚本管理
def oratk_tool_info_ora_shell(request):
    if request.method == 'GET':
        server_ip = '12.18.0.16'
        server_port = 22
        server_username = 'oracle'
        server_pwd = 'oracle'
        server_shell_file = '/home/oracle/dbmonitor/ora'
        # server_ip = '10.1.3.128'
        # server_port = 22
        # server_username = 'liweikang'
        # server_pwd = 'liweikang'
        # server_shell_file = '/home/liweikang/1.txt'

        try:
            ##ssh连接
            client = paramiko.SSHClient()
            client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            ##连接服务器
            client.connect(hostname=server_ip, port=server_port, username=server_username, password=server_pwd)
            ##拼装命令
            command = 'cat ' + server_shell_file
            # print(command)
            stdin, stdout, stderr = client.exec_command(str(command))
            res_suc = str(stdout.read(), 'utf-8')
            res_err = str(stderr.read(), 'utf-8')
            if request.session.get('is_login', None):
                ##关闭浏览器删除session
                request.session.set_expiry(0)
                if len(res_suc) > 0:
                    return render(request, 'oratk_app/oratk_tool_info_ora_shell.html',
                                  {'res_suc': res_suc, 'server_ip': server_ip, 'server_shell_file': server_shell_file})
                else:
                    return render(request, 'oratk_app/oratk_tool_info_ora_shell.html',
                                  {'res_err': res_err, 'server_ip': server_ip, 'server_shell_file': server_shell_file})
            else:
                return redirect('/basic_app/login.html')
        except Exception as e:
            res_err = e
            if request.session.get('is_login', None):
                ##关闭浏览器删除session
                request.session.set_expiry(0)
                return render(request, 'oratk_app/oratk_tool_info_ora_shell.html',
                              {'res_err': res_err, 'server_ip': server_ip, 'server_shell_file': server_shell_file})
            else:
                return redirect('/basic_app/login.html')


##ora工具脚本管理
def oratk_tool_info_ora_shell_edit(request):
    server_ip = '12.18.0.16'
    server_port = 22
    server_username = 'oracle'
    server_pwd = 'oracle'
    server_shell_file = '/home/oracle/dbmonitor/ora'
    if request.method == 'POST':
        # server_ip = '10.1.3.128'
        # server_port = 22
        # server_username = 'liweikang'
        # server_pwd = 'liweikang'
        # server_shell_file = '/home/liweikang/1.txt'
        shell_content = request.POST.get('shell_conent', None)
        print(shell_content)
        # 将修改后ora文件内容保存到数据库中
        cron_models.ora_shell_conent_version.objects.create(server_ip=server_ip, server_port=server_port,
                                                            shell_path=server_shell_file,
                                                            shell_conent=shell_content)

        ##将修改后的ora文件内容本地文件中
        with open('/Users/liwk/py/oratk_web/templates/shell_content/ora', 'w', encoding='utf-8') as f:
            f.write(shell_content.replace('\r\n', '\n'))
        try:
            ##sftp使用
            client = paramiko.Transport(server_ip, server_port)
            client.connect(username=server_username, password=server_pwd)
            sftp = paramiko.SFTPClient.from_transport(client)
            sftp.put("/Users/liwk/py/oratk_web/templates/shell_content/ora", server_shell_file)
            client.close()
            return redirect("/oratk_app/oratk_tool_info_ora_shell")
        except Exception as e:
            res_err = e
            return HttpResponse("修改脚本内容出错,%s" % (res_err))
    if request.method == 'GET':
        print(22)

        # server_ip = '10.1.3.128'
        # server_port = 22
        # server_username = 'liweikang'
        # server_pwd = 'liweikang'
        # server_shell_file = '/home/liweikang/1.txt'
        try:
            ##ssh连接
            client = paramiko.SSHClient()
            client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            ##连接服务器
            client.connect(hostname=server_ip, port=server_port, username=server_username, password=server_pwd)
            ##拼装命令
            command = 'cat ' + server_shell_file
            print(command)
            stdin, stdout, stderr = client.exec_command(str(command))
            res_suc = str(stdout.read(), 'utf-8')
            res_err = str(stderr.read(), 'utf-8')
            if request.session.get('is_login', None):
                ##关闭浏览器删除session
                request.session.set_expiry(0)
                if len(res_suc) > 0:
                    return render(request, 'oratk_app/oratk_tool_info_ora_shell_edit.html',
                                  {'res_suc': res_suc, 'server_ip': server_ip, 'server_shell_file': server_shell_file})
                else:
                    return render(request, 'oratk_app/oratk_tool_info_ora_shell_edit.html',
                                  {'res_err': res_err, 'server_ip': server_ip, 'server_shell_file': server_shell_file})
            else:
                return redirect('/basic_app/login.html')
        except Exception as e:
            res_err = e
            if request.session.get('is_login', None):
                ##关闭浏览器删除session
                request.session.set_expiry(0)
                return render(request, 'oratk_app/oratk_tool_info_ora_shell_edit.html',
                              {'res_err': res_err, 'server_ip': server_ip, 'server_shell_file': server_shell_file})
            else:
                return redirect('/basic_app/login.html')


###ora工具shell执行  ajax和form访问
def oratk_select_ora_shell(request):
    if request.method == 'POST':

        submit_id = request.POST.get('submit_id', 1)
        args = request.POST.get('args', '')
        user_pw = 'dbmonitor/ghsdbmon1tor'
        ##获取数据库连接串
        select_conn_string = request.POST.get('conn_string', None)

        global res_suc
        res_suc = ''
        if select_conn_string == '使用脚本内实例':
            ##使用脚本内的实例
            conn_string = '使用脚本内实例'
            command = 'sh /home/oracle/dbmonitor/ora ' + args
        else:
            ##获取数据库名字
            dbname = select_conn_string.split('=')[0]
            ##获取数据库连接串
            conn_string = select_conn_string.split('=')[1]
            ##拼装命令
            command = 'sh /home/oracle/dbmonitor/ora -u ' + user_pw + '@' + conn_string + ' ' + args

        try:
            ##ssh连接
            client = paramiko.SSHClient()
            client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            ##连接服务器
            client.connect(hostname='12.18.0.16', port='22', username='oracle', password='oracle')
            ##拼装命令
            # command ='sh /home/oracle/dbmonitor/ora -u ' + user_pw +'@'+ conn_string + ' ' + args
            print(command)
            stdin, stdout, stderr = client.exec_command(str(command))
            ##命令执行结果,readlines是将结果弄成列表方式
            res_suc = str(stdout.read(), 'utf-8')
            # print(res_suc)
            res_err = str(stderr.read(), 'utf-8')
            print(res_err)
            client.close()
            ##ajax post提交
        except Exception as e:
            res_err = e
        if submit_id == '0':
            if len(res_suc) > 0:
                return render(request, 'oratk_app/oratk_table_ora_shell.html', {'res_suc': res_suc})
            else:
                return render(request, 'oratk_app/oratk_table_ora_shell.html', {'res_err': res_err})
        ##form post提交方式
        else:
            if len(res_suc) > 0:
                return render(request, 'oratk_app/oratk_select_use_ora_shell.html',
                              {'res_suc': res_suc, 'conn_string': conn_string, 'args': args})
            else:
                return render(request, 'oratk_app/oratk_select_use_ora_shell.html',
                              {'res_err': res_err, 'conn_string': conn_string, 'args': args})


### 巡检操作 ###
##锁与等待时间
def oratk_most_waitevent_lock(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)

            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='locked'))
            return render(request, 'oratk_app/oratk_most_event.html', {'instanceinfo_result': instanceinfo_result})
        else:
            return redirect('/basic_app/login.html')


# 执行计划
def oratk_most_plan(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)

            instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='locked')).order_by('dbname')
            return render(request, 'oratk_app/oratk_most_plan.html', {'instanceinfo_result': instanceinfo_result})
        else:
            return redirect('/basic_app/login.html')


###常用功能-执行计划 post与ajax
def oratk_most_plan_ajax(request):
    global select_conn_string
    global columns
    global result
    global dbname
    global conn_string
    global arg1
    global arc_c
    if request.method == 'POST':
        args_dic = {}
        submit_id = request.POST.get('submit_id', 1)
        ##获取数据库连接串
        select_conn_string = request.POST.get('conn_string', None)
        ##获取数据库名字
        dbname = select_conn_string.split('=')[0]
        ##获取数据库连接串
        conn_string = select_conn_string.split('=')[1]
        ##db连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()

        sql_keyword = request.POST.get('sql_keyword', None)
        sql_id = request.POST.get('sql_id', None)
        if len(sql_id) != 0 and len(sql_keyword) == 0:
            arg1 = sql_id
            tool_name = 'sqlxp_sqlid'
        else:
            arg1 = sql_keyword
            tool_name = 'sqltext_text'
        print(arg1)
        print(tool_name)
        # ##获取查询项目对应的sql
        # ##sql.sqltext 为对应的sql
        sql = cron_models.ora_point_info.objects.filter(name=tool_name).first()
        sqltextlist = ast.literal_eval(sql.sqltext.replace('\r', '').replace('\n', ''))
        print(sqltextlist)

        args_dic['select_name'] = tool_name
        args_dic['dbname'] = dbname
        args_dic['select_conn_string'] = select_conn_string
        args_dic['conn_string'] = conn_string
        arc_c = 0
        for i in sqltextlist:
            args_dic['desc' + str(arc_c)] = i[0] + ':'
            # 替换sql
            sqltext = i[1].replace(':1', arg1)
            cursor.execute(sqltext)
            ##列名
            args_dic['columns' + str(arc_c)] = [col[0] for col in cursor.description]
            ##获取结果
            # result = cursor.fetchall()
            args_dic['result' + str(arc_c)] = cursor.fetchall()

            arc_c = arc_c + 1

        if submit_id == '0':
            return render(request, 'oratk_app/oratk_table_ora.html', args_dic)
        else:
            return render(request, 'oratk_app/oratk_select_use_ora.html', args_dic)

    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')


###常用功能-等待事件 post与ajax
def oratk_most_event_ajax(request):
    global select_conn_string
    global columns
    global result
    global dbname
    global conn_string
    global arg1
    global arg2
    global arc_c
    global sqllist
    global args_dic
    if request.method == 'POST':
        sid = request.POST.get('sid', None)
        ins_id = request.POST.get('ins_id', None)
        args_dic = {}
        submit_id = request.POST.get('submit_id', 1)
        ##获取数据库连接串
        select_conn_string = request.POST.get('conn_string', None)
        ##获取数据库名字
        dbname = select_conn_string.split('=')[0]
        ##获取数据库连接串
        conn_string = select_conn_string.split('=')[1]
        ##db连接
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()

        args_dic['dbname'] = dbname
        args_dic['select_conn_string'] = select_conn_string
        args_dic['conn_string'] = conn_string
        arc_c = 0
        if len(sid) > 0:
            args_dic['select_name'] = 'session_detail'
            # 当实例没有输入时，默认值为1
            if len(ins_id) == 0:
                arg2 = '1'
            else:
                arg2 = ins_id
            # ##获取查询项目对应的sql
            # ##sql.sqltext 为对应的sql
            # 当查询客户端信息时，获取sql语句
            sql_session_detail = cron_models.ora_point_info.objects.filter(name='session_detail').first()
            sql_session_detail = ast.literal_eval(sql_session_detail.sqltext.replace('\r', '').replace('\n', ''))
            sqllist = sql_session_detail
            for i in sqllist:
                args_dic['desc' + str(arc_c)] = i[0] + ':'
                arg1 = sid
                # 获取sql文本
                sqltext = i[1].replace(':1', arg1).replace(':2', arg2)
                print(sqltext)
                cursor.execute(sqltext)
                ##列名
                args_dic['columns' + str(arc_c)] = [col[0] for col in cursor.description]
                ##获取结果
                # result = cursor.fetchall()
                args_dic['result' + str(arc_c)] = cursor.fetchall()

                arc_c = arc_c + 1
            if submit_id == '0':
                return render(request, 'oratk_app/oratk_table_ora_session.html', args_dic)
            else:
                return render(request, 'oratk_app/oratk_select_use_ora.html', args_dic)

        else:
            args_dic['select_name'] = 'event_wait lock cur_date'
            # 当查询锁与等待事件时，获取sql语句
            sql_event = cron_models.ora_point_info.objects.filter(name='event_wait').first()
            sql_event = ast.literal_eval(sql_event.sqltext.replace('\r', '').replace('\n', ''))
            sql_lock = cron_models.ora_point_info.objects.filter(name='lock').first()
            sql_lock = ast.literal_eval(sql_lock.sqltext.replace('\r', '').replace('\n', ''))
            sql_date = cron_models.ora_point_info.objects.filter(name='cur_date').first()
            sql_date = ast.literal_eval(sql_date.sqltext.replace('\r', '').replace('\n', ''))
            sql_session_detail_lock = cron_models.ora_point_info.objects.filter(name='session_detail_lock').first()
            sql_session_detail_lock = ast.literal_eval(
                sql_session_detail_lock.sqltext.replace('\r', '').replace('\n', ''))

            sqllist = sql_date + sql_lock + sql_session_detail_lock + sql_event

            for i in sqllist:
                args_dic['desc' + str(arc_c)] = i[0] + ':'
                # 获取sql文本
                sqltext = i[1]
                cursor.execute(sqltext)
                ##列名
                args_dic['columns' + str(arc_c)] = [col[0] for col in cursor.description]
                ##获取结果
                # result = cursor.fetchall()
                args_dic['result' + str(arc_c)] = cursor.fetchall()

                arc_c = arc_c + 1
            if submit_id == '0':
                return render(request, 'oratk_app/oratk_table_ora.html', args_dic)
            else:
                return render(request, 'oratk_app/oratk_select_use_ora.html', args_dic)

    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')


#####################巡检#################
# def monitor(request):
#     if request.method == 'POST':
#         pass
#     if request.method == 'GET':
#         if request.session.get('is_login', None):
#             ##关闭浏览器删除session
#             request.session.set_expiry(0)
#             return render(request, 'oratk_app/monitor.html')
#         else:
#             return redirect('/basic_app/login.html')

#
# def command_exec_val(*args):
#     val1 = args[0]
#     val2 = args[1]
#     if len(val2) == 0:
#         return

##即时巡检
def oratk_monitor_immediate(request):
    if request.method == 'POST':
        curr_time2 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        ##获取要查询的数据库连接串
        # select_conn_string.split()[1] 为对应的连接串
        select_conn_string = request.POST.get('conn_string', None)
        dbname = select_conn_string.split('=')[0]
        conn_string = select_conn_string.split('=')[1]
        ip = re.findall(r"\b(?:[0-9]{1,3}\.){3}[0-9]{1,3}\b", conn_string)
        ip = ip[0]
        batch_id = time.strftime('%Y%m%d%H%M%S', time.localtime(time.time()))
        cron_models.oratk_monitor_immediate.objects.create(batch_id=batch_id, start_time=curr_time2, db_name=dbname,
                                                           db_conn=conn_string, status='running',
                                                                end_time='', sub_time='', remake='')
        ##下面为执行主机部分巡检
        ##ssh执行命令
        try:
            ##ssh连接
            client = paramiko.SSHClient()
            client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            ##连接服务器
            client.connect(hostname=ip, port=22, username='dbmonitor', password='dbmonitor')
            ####拼装命令
            ##os信息
            # os版本
            os_version = 'cat /etc/issue | head -1'

            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(os_version))
            os_version_val = str(stdout.read(), 'utf-8')
            os_version_err = str(stderr.read(), 'utf-8')
            if len(os_version_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='os_version',
                                                                  err_info=os_version_err,
                                                                  command_name=os_version)

            # os位数
            os_bit = 'getconf LONG_BIT'
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(os_bit))
            os_bit_val = str(stdout.read(), 'utf-8')
            os_bit_err = str(stderr.read(), 'utf-8')
            if len(os_bit_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='os_bit',
                                                                  err_info=os_bit_err,
                                                                  command_name=os_bit)

            # 是否为虚拟机
            computer_type = 'cat /proc/scsi/scsi | wc -l '
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(computer_type))
            computer_type_val = str(stdout.read(), 'utf-8')
            if int(computer_type_val) > 1:
                computer_type_val = '物理机'
            else:
                computer_type_val = '虚拟机'
            computer_type_err = str(stderr.read(), 'utf-8')
            if len(computer_type_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='computer_type',
                                                                  err_info=computer_type_err,
                                                                  command_name=computer_type)

            ##os_version巡检结果保存表中
            cron_models.oratk_monitor_info_os.objects.create(batch_id=batch_id, monitor_time=curr_time2,
                                                             os_name=os_version_val, os_bit=os_bit_val,
                                                             computer_type=computer_type_val)

            ####cpu巡检
            # cpu个数
            cpu_count = "cat /proc/cpuinfo | grep 'physical id' | sort | uniq | wc -l"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(cpu_count))
            cpu_count_val = str(stdout.read(), 'utf-8')
            cpu_count_err = str(stderr.read(), 'utf-8')
            if len(cpu_count_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='cpu_count',
                                                                  err_info=cpu_count_err,
                                                                  command_name=cpu_count)

            # cpu物理核心数
            cpu_core_physics = "cat /proc/cpuinfo |grep 'physical id' | sort -u |wc -l"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(cpu_core_physics))
            cpu_core_physics_val = str(stdout.read(), 'utf-8')
            cpu_core_physics_err = str(stderr.read(), 'utf-8')
            if len(cpu_core_physics_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='cpu_core_physics',
                                                                  err_info=cpu_core_physics_err,
                                                                  command_name=cpu_core_physics)

            # cpu逻辑核心
            cpu_core_logic = "cat /proc/cpuinfo |grep 'processor'  |wc -l"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(cpu_core_logic))
            cpu_core_logic_val = str(stdout.read(), 'utf-8')
            cpu_core_logic_err = str(stderr.read(), 'utf-8')
            if len(cpu_core_logic_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='cpu_core_logic',
                                                                  err_info=cpu_core_logic_err,
                                                                  command_name=cpu_core_logic)

            # cpu使用率
            cpu_rate = "top -n 1 -b | awk -F ' ' 'NR==3 {print $2}' | awk -F '%' '{print $1}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(cpu_rate))
            cpu_rate_val = str(stdout.read(), 'utf-8').replace('\n', '')
            # cpu_rate_val = round((int(float(cpu_rate_val)) * 100),2)
            cpu_rate_err = str(stderr.read(), 'utf-8')
            if len(cpu_rate_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='cpu_rate',
                                                                  err_info=cpu_rate_err,
                                                                  command_name=cpu_rate)
            # cpu 1分钟load
            cpu_load_1 = "top -n 1 -b | awk -F ' ' 'NR==1 {print $12}' |  awk -F ',' '{print $1}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(cpu_load_1))
            cpu_load_1_val = str(stdout.read(), 'utf-8')
            cpu_load_1_err = str(stderr.read(), 'utf-8')
            if len(cpu_load_1_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='cpu_load_1',
                                                                  err_info=cpu_load_1_err,
                                                                  command_name=cpu_load_1)
            # cpu 5分钟load
            cpu_load_5 = "top -n 1 -b | awk -F ' ' 'NR==1 {print $13}' |  awk -F ',' '{print $1}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(cpu_load_5))
            cpu_load_5_val = str(stdout.read(), 'utf-8')
            cpu_load_5_err = str(stderr.read(), 'utf-8')
            if len(cpu_load_5_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='cpu_load_5',
                                                                  err_info=cpu_load_5_err,
                                                                  command_name=cpu_load_5)

            ##将cpu信息保存到表中
            cron_models.oratk_monitor_info_cpu.objects.create(batch_id=batch_id, monitor_time=curr_time2,
                                                              cpu_count=cpu_count_val,
                                                              cpu_core_physics=cpu_core_physics_val,
                                                              cpu_core_logic=cpu_core_logic_val, use_rate_perc=cpu_rate_val,
                                                              load_1=cpu_load_1_val, load_5=cpu_load_5_val)

            ####mem巡检
            # mem_free = "cat /proc/meminfo | grep -E '^MemFree|^Buffers|^Cached|'  | awk '{ sum += $2 } END { print sum }'"
            mem_info = "cat /proc/meminfo | grep -E '^MemTotal|^MemFree|^Buffers|^Cached'  | awk '{print $2}' |xargs echo"

            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(mem_info))
            mem_info_val = str(stdout.read(), 'utf-8')
            mem_info_err = str(stderr.read(), 'utf-8')
            if len(mem_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='mem_info',
                                                                  err_info=mem_info_err,
                                                                  command_name=mem_info)
            # 内存总数
            mem_total = int(mem_info_val.split()[0])
            # free
            mem_free = int(mem_info_val.split()[1]) + int(mem_info_val.split()[2]) + int(mem_info_val.split()[3])

            # 内存使用率
            mem_rate = round((((mem_total - mem_free) / mem_total) * 100), 2)

            # swap内存信息
            swap_info = "cat /proc/meminfo | grep -E '^SwapTotal|^SwapFree' | awk '{print $2}' | xargs echo"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(swap_info))
            swap_info_val = str(stdout.read(), 'utf-8')
            swap_info_err = str(stderr.read(), 'utf-8')
            if len(swap_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='swap_info',
                                                                  err_info=swap_info_err,
                                                                  command_name=swap_info)
            swap_total = int(swap_info_val.split()[0])
            swap_free = int(swap_info_val.split()[1])
            # swap使用率
            swap_rate = round((((swap_total - swap_free) / swap_total) * 100), 2)

            # 大页信息，hugepage
            hugepage_info = "cat /proc/meminfo | grep -E '^HugePages_Total|^HugePages_Free|^Hugepagesize'| awk '{print $2}' | xargs echo"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(hugepage_info))
            hugepage_info_val = str(stdout.read(), 'utf-8')
            hugepage_info_err = str(stderr.read(), 'utf-8')
            if len(hugepage_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='hugepage_info',
                                                                  err_info=hugepage_info_err,
                                                                  command_name=hugepage_info)
            hugepage_total = hugepage_info_val.split()[0]
            hugepage_free = hugepage_info_val.split()[1]
            hugepagesize = hugepage_info_val.split()[2]

            cron_models.oratk_monitor_info_mem.objects.create(batch_id=batch_id, monitor_time=curr_time2,
                                                              mem_total_kb=mem_total,
                                                              mem_use_rate_perc=mem_rate,
                                                              swap_total_kb=swap_total,
                                                              swap_use_rate_perc=swap_rate,
                                                              hugepage_total_kb=hugepage_total,
                                                              hugepage_free_kb=hugepage_free,
                                                              hugepagesize_kb=hugepagesize)

            ####disk巡检
            # disk空间信息
            disk_space_info = "df -h  -l  | sed  '1d;/ /!N;s/\\n//;s/ \+/ /;' |  awk '{print $6,$2,$5}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(disk_space_info))
            disk_space_info_val = stdout.readlines()
            # print(disk_space_info_val)
            disk_space_info_err = str(stderr.read(), 'utf-8')
            if len(disk_space_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='disk_space_info',
                                                                  err_info=disk_space_info_err,
                                                                  command_name=disk_space_info)
            for res in disk_space_info_val:
                dir_path = res.split()[0]
                dir_space = res.split()[1]
                space_use_rate = res.split()[2].split('%')[0]
                cron_models.oratk_monitor_info_disk.objects.create(batch_id=batch_id, monitor_time=curr_time2,
                                                                   dir_path=dir_path,
                                                                   dir_space_g=dir_space,
                                                                   space_use_rate_perc=space_use_rate)

            # disk inode信息
            disk_inode_info = "df -i  -l  | sed  '1d;/ /!N;s/\\n//;s/ \+/ /;' |  awk '{print $6,$2,$5}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(disk_inode_info))
            disk_inode_info_val = stdout.readlines()
            disk_inode_info_err = str(stderr.read(), 'utf-8')
            if len(disk_inode_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='disk_inode_info',
                                                                  err_info=disk_inode_info_err,
                                                                  command_name=disk_inode_info)
            for res in disk_inode_info_val:
                #print(res)
                dir_path = res.split()[0]
                inode_count = res.split()[1]
                inode_use_rate = res.split()[2].split('%')[0]
                ##根据目录和批次把inode信息，update到表中
                id_val = cron_models.oratk_monitor_info_disk.objects.filter(dir_path=dir_path,
                                                                            batch_id=batch_id).first()
                cron_models.oratk_monitor_info_disk.objects.filter(id=id_val.id).update(inode_count=inode_count,
                                                                                        inode_use_rate_perc=inode_use_rate)

            # disk io信息
            disk_io_info = "iostat -x 1 1 -m | sed '1,6d'|sed '/^$/d' | awk '{print $1,$4,$5,$6,$7,$10,$11,$12}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(disk_io_info))
            disk_io_info_val = stdout.readlines()
            disk_io_info_err = str(stderr.read(), 'utf-8')
            if len(disk_io_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='disk_io_info',
                                                                  err_info=disk_io_info_err,
                                                                  command_name=disk_io_info)
            for res in disk_io_info_val:
                disk_name = res.split()[0]
                r_exec_count = res.split()[1]
                w_exec_count = res.split()[2]
                r_space = res.split()[3]
                w_space = res.split()[4]
                io_await = res.split()[5]
                io_svctm = res.split()[6]
                io_util = res.split()[7]
                cron_models.oratk_monitor_info_disk_io.objects.create(batch_id=batch_id, monitor_time=curr_time2,
                                                                   disk_name=disk_name,
                                                                   r_exec_count=r_exec_count,
                                                                   w_exec_count=w_exec_count,
                                                                   r_space_mb_s=r_space,w_space_mb_s=w_space,io_await_ms=io_await,
                                                                   io_svctm_ms=io_svctm,io_util_perc=io_util)
            # network 信息
            network_io_info = "sar -n DEV 1 1 | grep Average | sed '1d' | awk '{print $2,$5,$6}'"
            ##执行命令
            stdin, stdout, stderr = client.exec_command(str(network_io_info))
            network_io_info_val = stdout.readlines()
            network_io_info_err = str(stderr.read(), 'utf-8')
            if len(network_io_info_err) != 0:
                cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id, monitor_name='network_io_info',
                                                                  err_info=network_io_info_err,
                                                                  command_name=network_io_info)
            for res in network_io_info_val:
                interface_name = res.split()[0]
                up_io = res.split()[1]
                down_io = res.split()[2]
                cron_models.oratk_monitor_info_network.objects.create(batch_id=batch_id, monitor_time=curr_time2,
                                                                      interface_name=interface_name,
                                                                      up_kbyte_s=up_io,
                                                                      down_kbyte_s=down_io)



            ##将此批次主机的结果保存到
            os_version_info = cron_models.oratk_monitor_info_os.objects.values('os_name', 'os_bit',
                                                                               'computer_type').filter(
                batch_id=batch_id)
            os_version_info = list(os_version_info)
            cpu_info = cron_models.oratk_monitor_info_cpu.objects.values('cpu_count', 'cpu_core_physics',
                                                                         'cpu_core_logic', 'use_rate_perc', 'load_1',
                                                                         'load_5').filter(batch_id=batch_id)
            cpu_info = list(cpu_info)
            mem_info = cron_models.oratk_monitor_info_mem.objects.values('mem_total_kb', 'mem_use_rate_perc',
                                                                         'swap_total_kb', 'swap_use_rate_perc',
                                                                         'hugepage_total_kb', 'hugepage_free_kb',
                                                                         'hugepagesize_kb').filter(batch_id=batch_id)
            mem_info = list(mem_info)
            disk_space_info = cron_models.oratk_monitor_info_disk.objects.values('dir_path', 'dir_space_g',
                                                                                 'space_use_rate_perc', 'inode_count',
                                                                                 'inode_use_rate_perc').filter(
                batch_id=batch_id)
            disk_space_info = list(disk_space_info)
            disk_io_info = cron_models.oratk_monitor_info_disk_io.objects.values('disk_name', 'r_exec_count',
                                                                                 'w_exec_count', 'r_space_mb_s',
                                                                                 'w_space_mb_s', 'io_await_ms',
                                                                                 'io_svctm_ms', 'io_util_perc').filter(
                batch_id=batch_id)
            disk_io_info = list(disk_io_info)
            network_info = cron_models.oratk_monitor_info_network.objects.values('interface_name', 'up_kbyte_s',
                                                                                 'down_kbyte_s').filter(
                batch_id=batch_id)
            network_info = list(network_info)
            point_info = {'os_version': os_version_info, 'cpu': cpu_info,
                          'memory': mem_info, 'disk': disk_space_info, 'disk_io': disk_io_info,
                          'network': network_info}
            ###将数据结果以字典方式写入表
            i = 0
            for title_info, res_list in point_info.items():
                host_rec = {}
                # 标题
                host_rec['title_info_' + str(i)] = title_info


                # 列名和值
                col = []
                #print(res_list)
                for k, v in res_list[0].items():
                    ##列名
                    col.append(k)
                    host_rec['host_columns' + str(i)] = col
                val = []
                for row_dic in res_list:
                    val_row = []
                    for k, v in row_dic.items():
                        ##列名
                        val_row.append(v)
                    val.append(val_row)
                host_rec['val' + str(i)] = val
                cron_models.oratk_monitor_host_record.objects.create(batch_id=batch_id,monitor_name=title_info,
                                                                     monitor_record=host_rec)
                i = i + 1
        ##ssh异常处理
        except Exception as e:
            res_err = e
            print('Exception: %s' %res_err)
            cron_models.oratk_monitor_info_err.objects.create(batch_id=batch_id,monitor_name='SSH_ERR',err_info=res_err)
        ##以上为主机部分巡检


        ##下面执行为数据库部分巡检
        db = cx_Oracle.connect('dbmonitor', 'ghsdbmon1tor', select_conn_string.split('=')[1])
        cursor = db.cursor()

        ##定义数据库巡检项
        db_monitor_point = ['cur_date','dbinfo','sga_info','pga_info','lock','event_overview',
                            'event_stats','tablespace','temp_tablespace','redo_switch_2d','transaction','cursor_share']
        # , 'sga_info', 'pga_info','lock','event_overview','event_stats','tablespace','temp_tablespace',
        #                     'redo_switch_2d','transaction','job','cursor_share']
        sqllist = []
        for point in db_monitor_point:
            #print(point)
            sql = cron_models.ora_point_info.objects.filter(name=point).first()
            #print(sql.sqltext)
            sql = ast.literal_eval(sql.sqltext.replace('\r', '').replace('\n', ''))
            sqllist = sqllist + sql
        #sqllist = sql_date + sql_lock + sql_session_detail_lock + sql_event
        sql_exec_c = 0
        for i in sqllist:
            sql_res = {}
            sql_res['desc' + str(sql_exec_c)] = i[0] + ':'
            # 获取sql文本
            sqltext = i[1]
            cursor.execute(sqltext)
            ##列名
            sql_res['columns' + str(sql_exec_c)] = [col[0] for col in cursor.description]
            ##获取结果
            # result = cursor.fetchall()
            sql_res['result' + str(sql_exec_c)] = cursor.fetchall()

            sql_exec_c = sql_exec_c + 1
            cron_models.oratk_monitor_sql_record.objects.create(batch_id=batch_id,monitor_name=i[0],monitor_record=sql_res)

        ##以上为数据库部分巡检



        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)

            ##计算耗时
            start_time = cron_models.oratk_monitor_immediate.objects.filter(batch_id=batch_id).first()
            end_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            seconds = (parse(end_time) - parse(start_time.start_time)).seconds

            ##如果巡检存在错误
            err_info = cron_models.oratk_monitor_info_err.objects.values('batch_id', 'monitor_name', 'err_info').filter(
                batch_id=batch_id)
            if len(list(err_info)) > 0:
                cron_models.oratk_monitor_immediate.objects.filter(batch_id=batch_id).update(status='error',sub_time=seconds)
            else:
                cron_models.oratk_monitor_immediate.objects.filter(batch_id=batch_id).update(status='success',sub_time=seconds)
            return redirect('/oratk_app/oratk_monitor_immediate')
        else:
            return redirect('/basic_app/login.html')


    if request.method == 'GET':
        record_result = cron_models.oratk_monitor_immediate.objects.all().order_by('-start_time')
        instanceinfo_result = basic_models.instance_info.objects.filter(~Q(status='locked')).order_by('dbname')
        currentpage_num = int(request.GET.get('page', '1'))
        line_count_set = int(request.GET.get('line_count', 5))
        user_search = request.GET.get('user_search', 'all')
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            v_paginator(record_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_monitor_immediate.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num
                              , 'search': user_search, 'instanceinfo_result': instanceinfo_result})
        else:
            return redirect('/basic_app/login.html')



##巡检详细报告
def oratk_monitor_immediate_report_info(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            batch_id = request.GET.get('batch_id')
            db_info = cron_models.oratk_monitor_immediate.objects.filter(batch_id=batch_id).first()
            ##判断巡检中是否有错误
            err_info = cron_models.oratk_monitor_info_err.objects.values('batch_id','monitor_name','err_info').filter(batch_id=batch_id)
            if len(list(err_info)) > 0:
                return render(request, 'oratk_app/oratk_monitor_immediate_report_info_err.html', {'err_info':list(err_info),'db_info':db_info})
            else:
                # os_version_info = cron_models.oratk_monitor_info_os.objects.values('os_name','os_bit','computer_type').filter(batch_id=batch_id)
                # os_version_info = list(os_version_info)
                # cpu_info = cron_models.oratk_monitor_info_cpu.objects.values('cpu_count','cpu_core_physics','cpu_core_logic','use_rate_perc','load_1','load_5').filter(batch_id=batch_id)
                # cpu_info = list(cpu_info)
                # mem_info = cron_models.oratk_monitor_info_mem.objects.values('mem_total_kb','mem_use_rate_perc','swap_total_kb','swap_use_rate_perc','hugepage_total_kb','hugepage_free_kb','hugepagesize_kb').filter(batch_id=batch_id)
                # mem_info = list(mem_info)
                # disk_space_info = cron_models.oratk_monitor_info_disk.objects.values('dir_path','dir_space_g','space_use_rate_perc','inode_count','inode_use_rate_perc').filter(batch_id=batch_id)
                # disk_space_info = list(disk_space_info)
                # disk_io_info = cron_models.oratk_monitor_info_disk_io.objects.values('disk_name','r_exec_count','w_exec_count','r_space_mb_s','w_space_mb_s','io_await_ms','io_svctm_ms','io_util_perc').filter(batch_id=batch_id)
                # disk_io_info = list(disk_io_info)
                # network_info = cron_models.oratk_monitor_info_network.objects.values('interface_name','up_kbyte_s','down_kbyte_s').filter(batch_id=batch_id)
                # network_info = list(network_info)
                # point_info = {'os_version_info':os_version_info,'cpu_info':cpu_info,
                #               'mem_info':mem_info,'disk_space_info':disk_space_info,'disk_io_info':disk_io_info,
                #               'network_info':network_info}

                # ###主机模板
                # res = {'db_info':db_info}
                # i = 0
                # for title_info,res_list in point_info.items():
                #     #标题
                #     res['title_info_' + str(i)] = title_info
                #
                #
                #     #列名和值
                #     col = []
                #     for k,v in res_list[0].items():
                #         ##列名
                #         col.append(k)
                #         res['host_columns' + str(i)] = col
                #     val = []
                #     for row_dic in  res_list:
                #         val_row = []
                #         for k,v in row_dic.items():
                #             ##列名
                #             val_row.append(v)
                #         val.append(val_row)
                #     res['val' + str(i)] = val
                #     i = i + 1
                ##以上是主机数据字典


                res = {'db_info': db_info}

                ##以下是获取主机结果字典
                host_rec = cron_models.oratk_monitor_host_record.objects.filter(batch_id=batch_id)
                host_rec_dic = {}
                for rec_dic in host_rec:
                    host_rec_dic = dict(host_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                res = dict(res, **host_rec_dic)

                ##以下是sql数据字典
                sql_rec = cron_models.oratk_monitor_sql_record.objects.filter(batch_id=batch_id)
                sql_rec_dic = {}
                for rec_dic in sql_rec:
                    sql_rec_dic = dict(sql_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                ##以上是sql数据字典
                res = dict(res, **sql_rec_dic)
                # print(res)
                return render(request, 'oratk_app/oratk_monitor_immediate_report_info.html',res)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')


##巡检报告(report)
def oratk_monitor_immediate_report(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            batch_id = request.GET.get('batch_id')
            db_name = request.GET.get('db_name')
            limit_res = cron_models.oratk_monitor_sql_host_limit.objects.all()
            db_info = cron_models.oratk_monitor_immediate.objects.filter(batch_id=batch_id).first()
            ##判断是否生成过report
            is_report = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id)
            ##已有报告
            report_limit_change = []
            print(1)
            if is_report.exists():
                print(2)
                ##比较上次生成报告时的阀值与现在模板里阀值比较有没有变化
                source_exec_limit = cron_models.oratk_monitor_report_exec_sql_limit.objects.filter(batch_id=batch_id)
                for source_exec_limit_row in source_exec_limit:
                    model_limit = cron_models.oratk_monitor_sql_host_limit.objects.filter(monitor_name=source_exec_limit_row.monitor_name,
                                                                                                  monitor_point=source_exec_limit_row.monitor_point).first()
                    # print(source_exec_limit_row.limit_values)
                    # print(model_limit.limit_values)
                    if source_exec_limit_row.limit_values != model_limit.limit_values:
                        report_limit_change.append('1')
                if len(report_limit_change) == 0:
                    ##如果有报告且阀值信息没变，就返回之前的报告内容
                    ##以下是sql数据字典
                    res = {'db_info': db_info,'limit_res':limit_res}
                    time_record = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id).first()
                    res['time_record'] = time_record.monitor_make_time
                    # sql_rec = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id)
                    # sql_rec_dic = {}
                    # sql_rec_row_count = 0
                    # for rec_dic in sql_rec:
                    #     sql_rec_dic = dict(sql_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                    #     sql_rec_dic['limit' + str(sql_rec_row_count)] = '( limit: '+ rec_dic.limit_values + ')'
                    #     sql_rec_row_count = sql_rec_row_count + 1
                    # ##以上是sql数据字典
                    # res = dict(res, **sql_rec_dic)
                    # print(res)
                    ##获取sql的数据字典结果集
                    sql_rec = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id,
                                                                              monitor_type='database')
                    sql_rec_dic = {}
                    # sql_rec_row_count = 0
                    if sql_rec.exists:
                        for rec_dic in sql_rec:
                            # if rec_dic.monitor_type_type == 'database':
                            sql_rec_dic = dict(sql_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                            # 取limit值
                            sql_rec_dic['limit' + rec_dic.seq_record] = '( limit: ' + rec_dic.limit_values + ')'
                    ##获取host类型的报告记录
                    host_res = cron_models.oratk_monitor_report.objects.filter(~Q(host_limit=None),
                                                                               batch_id=batch_id,
                                                                               monitor_type='host')
                    if host_res.exists():
                        host_rec_dic = {}
                        # host_rec_row_count = 0
                        for rec_dic in host_res:
                            host_rec_dic = dict(host_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                            #     # 取limit值
                            host_rec_dic['host_limit_' + rec_dic.seq_record] = rec_dic.host_limit
                            # host_rec_dic['host_limit_' + str(host_rec_row_count)] = rec_dic.host_limit
                            # host_rec_row_count = host_rec_row_count + 1
                    res = dict(res, **sql_rec_dic)
                    res = dict(res, **host_rec_dic)
                    return render(request, 'oratk_app/oratk_monitor_immediate_report.html', res)

            ##生成新报告
            if is_report.count() == 0 or len(report_limit_change) > 0:
                ##如果是阀值发生改变，清空report记录和阀值记录
                if len(report_limit_change) > 0:
                    cron_models.oratk_monitor_report_exec_sql_limit.objects.filter(batch_id=batch_id).delete()
                    cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id).delete()
                ##获取阀值项
                limit_res = cron_models.oratk_monitor_sql_host_limit.objects.all().order_by('monitor_type')
                ##把当前阀值记录到表
                curr_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
                for limit_info in limit_res:
                    cron_models.oratk_monitor_report_exec_sql_limit.objects.create(batch_id=batch_id,db_name=db_name,monitor_name=limit_info.monitor_name,
                                                                                   monitor_point=limit_info.monitor_point,
                                                                                   limit_values=limit_info.limit_values,
                                                                                   )
                i = 0
                for limit_row in limit_res:
                    limit_values = limit_row.limit_values
                    report_res = {}
                    ##主机模板
                    if limit_row.monitor_type == 'host':
                        print('主机')
                        ##根据阀值项获取巡检详细记录
                        ##主机模板
                        print(limit_row.monitor_point)
                        host_res = cron_models.oratk_monitor_host_record.objects.filter(batch_id=batch_id,
                                                                                        monitor_name=limit_row.monitor_name).first()
                        host_res = ast.literal_eval(host_res.monitor_record)
                        report_res['title_info_' + str(i)] = [ii for ii in host_res.values()][0]
                        report_res['host_columns' + str(i)] = [ii for ii in host_res.values()][1]
                        host_record_res = []
                        ##cpu
                        if limit_row.monitor_name == 'cpu':
                            if limit_row.monitor_point == 'load_1':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[3]) > float(limit_values):
                                        host_record_res.append(iii)
                            if limit_row.monitor_point == 'load_5':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[4]) > float(limit_values):
                                        host_record_res.append(iii)
                            if limit_row.monitor_point == 'use_rate_perc':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[5]) > float(limit_values):
                                        host_record_res.append(iii)
                            if host_record_res:
                                report_res['val' + str(i)] = host_record_res
                                cron_models.oratk_monitor_report.objects.create(batch_id=batch_id,
                                                                                    monitor_name=limit_row.monitor_name,
                                                                                    monitor_point=limit_row.monitor_point,
                                                                                    monitor_record=report_res,
                                                                                    limit_values=limit_values,
                                                                                    monitor_type='host',
                                                                                    seq_record=str(i),
                                                                                    monitor_make_time=curr_time)
                        ##mem
                        if limit_row.monitor_name == 'memory':
                            if limit_row.monitor_point == 'mem_use_rate_perc':
                                for iii in [ii for ii in host_res.values()][2]:
                                    print (iii[1])
                                    if float(iii[1]) > float(limit_values):
                                        host_record_res.append(iii)
                            if limit_row.monitor_point == 'swap_use_rate_perc':
                                for iii in [ii for ii in host_res.values()][2]:
                                    print(iii[3])
                                    if float(iii[3]) > float(limit_values):
                                        host_record_res.append(iii)
                            if host_record_res:
                                report_res['val' + str(i)] = host_record_res
                                cron_models.oratk_monitor_report.objects.create(batch_id=batch_id,
                                                                                    monitor_name=limit_row.monitor_name,
                                                                                    monitor_point=limit_row.monitor_point,
                                                                                    monitor_record=report_res,
                                                                                    limit_values=limit_values,
                                                                                    monitor_type='host',
                                                                                    seq_record=str(i),
                                                                                    monitor_make_time=curr_time)

                        ##disk
                        if limit_row.monitor_name == 'disk':
                            if limit_row.monitor_point == 'space_use_rate_perc':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[2]) > float(limit_values):
                                        host_record_res.append(iii)
                            if limit_row.monitor_point == 'inode_use_rate_perc':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[4]) > float(limit_values):
                                        host_record_res.append(iii)
                            if host_record_res:
                                report_res['val' + str(i)] = host_record_res
                                cron_models.oratk_monitor_report.objects.create(batch_id=batch_id,
                                                                                    monitor_name=limit_row.monitor_name,
                                                                                    monitor_point=limit_row.monitor_point,
                                                                                    monitor_record=report_res,
                                                                                    limit_values=limit_values,
                                                                                    monitor_type='host',
                                                                                    seq_record=str(i),
                                                                                    monitor_make_time=curr_time)


                        ##disk_io
                        if limit_row.monitor_name == 'disk_io':
                            if limit_row.monitor_point == 'io_util_perc':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[7]) > float(limit_values):
                                        host_record_res.append(iii)
                            if host_record_res:
                                report_res['val' + str(i)] = host_record_res
                                cron_models.oratk_monitor_report.objects.create(batch_id=batch_id,
                                                                                    monitor_name=limit_row.monitor_name,
                                                                                    monitor_point=limit_row.monitor_point,
                                                                                    monitor_record=report_res,
                                                                                    limit_values=limit_values,
                                                                                    monitor_type='host',
                                                                                    seq_record=str(i),
                                                                                    monitor_make_time=curr_time)


                        ##network
                        if limit_row.monitor_name == 'network':
                            if limit_row.monitor_point == 'up_kbyte_s':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[1]) > float(limit_values):
                                        host_record_res.append(iii)
                            if limit_row.monitor_point == 'down_kbyte_s':
                                for iii in [ii for ii in host_res.values()][2]:
                                    if float(iii[2]) > float(limit_values):
                                        host_record_res.append(iii)
                            if host_record_res:
                                report_res['val' + str(i)] = host_record_res
                                cron_models.oratk_monitor_report.objects.create(batch_id=batch_id,
                                                                                    monitor_name=limit_row.monitor_name,
                                                                                    monitor_point=limit_row.monitor_point,
                                                                                    monitor_record=report_res,
                                                                                    limit_values=limit_values,
                                                                                    monitor_type='host',
                                                                                    seq_record=str(i),
                                                                                    monitor_make_time=curr_time)

                    else:
                        print('数据库')
                        ##数据库模板
                        ##根据阀值项获取巡检详细记录
                        print(limit_row.monitor_point)
                        sql_res = cron_models.oratk_monitor_sql_record.objects.filter(batch_id=batch_id,
                                                                                      monitor_name=limit_row.monitor_point).first()

                        sql_res = ast.literal_eval(sql_res.monitor_record)
                        report_res['desc' + str(i)] = [ii for ii in sql_res.values()][0]
                        report_res['columns' + str(i)] = [ii for ii in sql_res.values()][1]
                        record_res = []
                        ###tablespace_use_rate
                        if limit_row.monitor_point == 'tablespace_use_rate':
                            ##循环每条记录
                            for iii in [ii for ii in sql_res.values()][2]:
                                if int(iii[5].split('%')[0]) > int(limit_values.split('%')[0]) :
                                    record_res.append(iii)
                       ###temp_tablespce_use_rate
                        if limit_row.monitor_point == 'temp_tablespce_use_rate':
                            ##循环每条记录
                            for iii in [ii for ii in sql_res.values()][2]:
                                if int(iii[3]) > int(limit_values.split('%')[0]) :
                                    record_res.append(iii)



                        report_res['result' + str(i)] = record_res
                        if record_res:
                            cron_models.oratk_monitor_report.objects.create(batch_id=batch_id,
                                                                            monitor_name=limit_row.monitor_name,
                                                                            monitor_point=limit_row.monitor_point,
                                                                            monitor_record=report_res,
                                                                            limit_values=limit_values,
                                                                            monitor_type='database',
                                                                            seq_record=str(i),
                                                                            monitor_make_time = curr_time)

                    i = i +1

                ##返回结果
                ##以下是sql和host的数据字典结果集
                res = {'db_info':db_info,'limit_res':limit_res}
                time_record = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id).first()
                #取报告生成时间
                res['time_record'] = time_record.monitor_make_time

                ##获取sql的数据字典结果集
                sql_rec = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id,monitor_type='database')
                sql_rec_dic = {}
                # sql_rec_row_count = 0
                if sql_rec.exists:
                    for rec_dic in sql_rec:
                        # if rec_dic.monitor_type_type == 'database':
                        sql_rec_dic = dict(sql_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                        print(rec_dic.monitor_record)
                        #取limit值
                        sql_rec_dic['limit' + rec_dic.seq_record] = '( limit: '+ rec_dic.limit_values + ')'

                        # sql_rec_dic['limit' + str(sql_rec_row_count)] = '( limit: '+ rec_dic.limit_values + ')'
                        # sql_rec_row_count = sql_rec_row_count + 1

                #获取host的数据字典结果集
                  ##如果一条记录有多个阀值的情况，将多条合并成一条，并把阀值信息统计到一起 seq_record
                  ##如果一条记录，也将阀值信息放到 seq_record
                join_list = ['cpu','memory','disk','disk_io','network']
                for i in join_list:
                   host_rec_i = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id,monitor_type='host',monitor_name=i)
                   if host_rec_i.exists():
                        limit_list = []
                        for row_i in host_rec_i:
                            limit_list.append(row_i.monitor_point + '_limit: ' + row_i.limit_values)
                        host_limit = cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id, monitor_type='host',
                                                                            monitor_name=i).first()
                        cron_models.oratk_monitor_report.objects.filter(batch_id=batch_id, monitor_type='host',
                                                                            monitor_name=i,monitor_point=host_limit.monitor_point).update(host_limit=limit_list)



                ##获取host类型的报告记录
                host_res = cron_models.oratk_monitor_report.objects.filter(~Q(host_limit=None),batch_id=batch_id,monitor_type='host')
                if host_res.exists():
                    host_rec_dic = {}
                    # host_rec_row_count = 0
                    for rec_dic in host_res:
                        host_rec_dic = dict(host_rec_dic, **(ast.literal_eval(rec_dic.monitor_record)))
                    #     # 取limit值
                        host_rec_dic['host_limit_' + rec_dic.seq_record] = rec_dic.host_limit
                        # host_rec_dic['host_limit_' + str(host_rec_row_count)] = rec_dic.host_limit
                        # host_rec_row_count = host_rec_row_count + 1
                res = dict(res, **sql_rec_dic)
                res = dict(res, **host_rec_dic)
                # print(res)
                return render(request, 'oratk_app/oratk_monitor_immediate_report.html', res)
        else:
            return redirect('/basic_app/login.html')

##每日巡检
def oratk_monitor_day(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')

##每周巡检
def oratk_monitor_week(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')


def oratk_monitor_mgr(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/monitor.html')
        else:
            return redirect('/basic_app/login.html')

##巡检阀值管理
def oratk_monitor_limit_mgr(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            if user_search != 'all':
                limit_res = cron_models.oratk_monitor_sql_host_limit.objects.filter(Q(monitor_name=user_search)
                                                                  | Q(limit_values=user_search)
                                                                  | Q(remark=user_search)).order_by('monitor_type')
                v_paginator(limit_res, currentpage_num, line_count_set)
            else:
                user_search = ''
                limit_res = cron_models.oratk_monitor_sql_host_limit.objects.all().order_by('monitor_type')
                v_paginator(limit_res, currentpage_num, line_count_set)
            return render(request, 'oratk_app/oratk_monitor_limit.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num
                              , 'search': user_search, 'limit_res': limit_res})
        else:
            return redirect('/basic_app/login.html')
##添加数据库阀值
def oratk_monitor_limit_add(request):
    if request.method == 'POST':
        monitor_name = request.POST.get('monitor_name')
        monitor_point = request.POST.get('monitor_point')
        add_limit = request.POST.get('add_limit')
        add_monitor_type = request.POST.get('add_monitor_type')
        add_remake = request.POST.get('add_remake')
        try:
            cron_models.oratk_monitor_sql_host_limit.objects.create(monitor_name=monitor_name,monitor_point=monitor_point, limit_values=add_limit,monitor_type=add_monitor_type,remark=add_remake)
            return redirect('/oratk_app/oratk_monitor_limit_mgr')
        except Exception as e:
                return HttpResponse("添加失败，报错信息: %s"%(e)   )
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            point_name = cron_models.ora_point_info.objects.all().order_by('name')
            return render(request,'oratk_app/oratk_monitor_limit_add.html',{'point_name_res':point_name})
        else:
            return redirect('/basic_app/login.html')

#添加主机阀值
def oratk_monitor_limit_add_host(request):
    if request.method == 'POST':
        monitor_name = request.POST.get('monitor_name')
        monitor_point = request.POST.get('monitor_point')
        add_limit = request.POST.get('add_limit')
        add_monitor_type = request.POST.get('add_monitor_type')
        add_remake = request.POST.get('add_remake')
        try:
            cron_models.oratk_monitor_sql_host_limit.objects.create(monitor_name=monitor_name,monitor_point=monitor_point,monitor_type=add_monitor_type,
                                                                    limit_values=add_limit,remark=add_remake)
            return redirect('/oratk_app/oratk_monitor_limit_mgr')
        except Exception as e:
                return HttpResponse("添加失败，报错信息: %s"%(e)   )
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            global  host_limit_dic
            host_limit_dic = {'cpu':['use_rate_perc','load_1','load_5'],'memory':['mem_use_rate_perc','swap_use_rate_perc'],
                              'disk':['space_use_rate_perc','inode_use_rate_perc'],'disk_io':['io_util_perc'],
                              'network':['up_kbyte_s','down_kbyte_s']}
            key_list = []
            for key in host_limit_dic.keys():
                key_list.append(key)

            return render(request,'oratk_app/oratk_monitor_limit_add_host.html',{'host_key_list':key_list})
        else:
            return redirect('/basic_app/login.html')



def oratk_monitor_limit_add_ajax(request):
    if request.method == 'POST':
        point_list = []
        monitor_name = request.POST.get('monitor_name')
        monitor_point_res = cron_models.ora_point_info.objects.filter(name=monitor_name).first()
        for point in ast.literal_eval(monitor_point_res.sqltext.replace('\r', '').replace('\n', '')):
            point_list.append(point[0])
        return render(request, 'oratk_app/oratk_monitor_limit_add_ajax.html', {'point_name':point_list})

def oratk_monitor_limit_add_host_ajax(request):
    if request.method == 'POST':
        monitor_name = request.POST.get('monitor_name')
        point_list = host_limit_dic[monitor_name]
        return render(request, 'oratk_app/oratk_monitor_limit_add_ajax.html', {'point_name':point_list})

##删除阀值信息
def oratk_monitor_limit_delete(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            cron_models.oratk_monitor_sql_host_limit.objects.filter(id=nid).delete()
            return redirect('/oratk_app/oratk_monitor_limit_mgr')
        else:
            return redirect('/basic_app/login.html')

##编辑阀值信息
def oratk_monitor_limit_edit(request):
    if request.method == 'POST':
        v_id = request.POST.get('id')
        add_limit = request.POST.get('add_limit')
        add_remake = request.POST.get('add_remake','')
        cron_models.oratk_monitor_sql_host_limit.objects.filter(id=v_id).update(limit_values=add_limit,remark=add_remake)
        return redirect('/oratk_app/oratk_monitor_limit_mgr')
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            print(nid)
            edit_res = cron_models.oratk_monitor_sql_host_limit.objects.filter(id=nid).first()
            return render(request,'oratk_app/oratk_monitor_limit_edit.html',{'edit_res':edit_res})
        else:
            return redirect('/basic_app/login.html')


#############以下 计划任务页-监控项管理 #############
def cron_point_mgr(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            print(user_search)
            ##当用户搜索时：
            if user_search != 'all':
                cron_point_info_result = cron_models.oratk_cron_point_info.objects.filter(Q(name__contains=user_search)
                                                                                          | Q(
                    desc__contains=user_search)
                                                                                          | Q(
                    sqltext__contains=user_search)
                                                                                          | Q(
                    limit__contains=user_search)
                                                                                          | Q(
                    status__contains=user_search)
                                                                                          | Q(
                    point_type__contains=user_search)
                                                                                          | Q(
                    remake__contains=user_search))
                v_paginator(cron_point_info_result, currentpage_num, line_count_set)

            # 当用户没有搜索时：
            else:
                user_search = ''
                cron_point_info_result = cron_models.oratk_cron_point_info.objects.all()
                v_paginator(cron_point_info_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/cron_point_mgr.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')


def cron_point_mgr_add(request):
    if request.method == 'POST':
        add_name = request.POST.get('add_name', None)
        add_sqltext = request.POST.get('add_sqltext', None)
        add_desc = request.POST.get('add_desc', None)
        add_limit = request.POST.get('add_limit', None)
        add_status = request.POST.get('add_status', None)
        add_remake = request.POST.get('add_remake', None)
        add_point_type = request.POST.get('add_point_type', None)
        cron_models.oratk_cron_point_info.objects.create(name=add_name, desc=add_desc, sqltext=add_sqltext,
                                                         limit=add_limit, status=add_status,
                                                         remake=add_remake, point_type=add_point_type)
        return redirect("/oratk_app/cron_point_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/cron_point_add.html')
        else:
            return redirect('/basic_app/login.html')


def cron_point_mgr_edit(request):
    if request.method == 'POST':
        v_id = request.POST.get('v_id', None)
        add_name = request.POST.get('add_name', None)
        add_desc = request.POST.get('add_desc', None)
        add_sqltext = request.POST.get('add_sqltext', None)
        add_limit = request.POST.get('add_limit', None)
        add_status = request.POST.get('add_status', None)
        add_remake = request.POST.get('add_remake', None)
        add_point_type = request.POST.get('add_point_type', None)
        cron_models.oratk_cron_point_info.objects.filter(id=v_id).update(name=add_name, desc=add_desc,
                                                                         sqltext=add_sqltext,
                                                                         limit=add_limit, status=add_status,
                                                                         remake=add_remake, point_type=add_point_type)
        return redirect("/oratk_app/cron_point_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            edit_result = cron_models.oratk_cron_point_info.objects.get(id=nid)
            return render(request, 'oratk_app/cron_point_edit.html', {'cron_point_info_result': edit_result})
        else:
            return redirect('/basic_app/login.html')


def cron_point_mgr_delete(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            point_info = cron_models.oratk_cron_point_info.objects.filter(id=nid).values('name').first()['name']
            try:
                task_point_info = \
                    cron_models.oratk_cron_task.objects.filter(task_point=point_info).values('task_point').first()[
                        'task_point']
                if task_point_info:
                    data = {'result': '不能删除该监控项目', 'msg': '该监控项目已被使用'}
                    return JsonResponse(data, json_dumps_params={'ensure_ascii': False})
                else:
                    # print(task_point_info)
                    cron_models.oratk_cron_point_info.objects.filter(id=nid).delete()
                    return redirect("/oratk_app/cron_point_mgr")
            except:
                cron_models.oratk_cron_point_info.objects.filter(id=nid).delete()
                return redirect("/oratk_app/cron_point_mgr")
        else:
            return redirect('/basic_app/login.html')


def cron_point_mgr_lock(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            cron_models.oratk_cron_point_info.objects.filter(id=nid).update(status='locked')
            return redirect("/oratk_app/cron_point_mgr")
        else:
            return redirect('/basic_app/login.html')


def cron_point_mgr_more(request):
    if request.method == 'POST':
        return redirect("/oratk_app/cron_point_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            more_result = cron_models.oratk_cron_point_info.objects.get(id=nid)
            return render(request, 'oratk_app/cron_point_more.html', {'more_result': more_result})
        else:
            return redirect('/basic_app/login.html')


#################以下 计划任务页-计划任务管理 #################
def cron_task_mgr(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            ##当用户搜索时：
            if user_search != 'all':
                cron_task_result = cron_models.oratk_cron_task.objects.filter(Q(task_info__contains=user_search)
                                                                              | Q(task_point__contains=user_search)
                                                                              | Q(task_time__contains=user_search)
                                                                              | Q(limit_status__contains=user_search)
                                                                              | Q(limit__contains=user_search)
                                                                              | Q(sms_status__contains=user_search)
                                                                              | Q(remake__contains=user_search)).values(
                    "id", "name", "name__description", "task_time",
                    "task_point", "task_info", "limit_status",
                    "limit", "limit_unit", "sms_status", 'name__date_changed',
                    "name__enabled", "remake", "instance", "sms_contact", "email_contact", "crtime", "chtime",
                    "task_type")
                v_paginator(cron_task_result, currentpage_num, line_count_set)
            # 当用户没有搜索时：
            else:
                user_search = ''
                cron_task_result = cron_models.oratk_cron_task.objects.all().values("id", "name", "name__description",
                                                                                    "task_time",
                                                                                    "task_point", "task_info",
                                                                                    "limit_status",
                                                                                    "limit", "limit_unit", "sms_status",
                                                                                    'name__date_changed',
                                                                                    "name__enabled", "remake",
                                                                                    "instance", "sms_contact",
                                                                                    "email_contact", "crtime", "chtime",
                                                                                    "task_type")
                # print(cron_task_result)
                v_paginator(cron_task_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/cron_task_mgr.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_add_sql(request):
    if request.method == 'POST':
        curr_time = time.strftime('%Y年%m月%d日 %H:%M:%S', time.localtime(time.time()))
        kwargs_dict = {}
        add_name = request.POST.get('add_name', None)
        add_desc = request.POST.get('add_desc', None)
        add_task_info = request.POST.get('add_tash_info', None)
        add_task_time = request.POST.get('add_task_time', None)
        add_task_point = request.POST.get('add_task_point', None)
        add_limit_status = request.POST.get('add_limit_status', None)
        add_limit = request.POST.get('add_limit', 'None')
        add_limit_unit = request.POST.get('add_limit_unit', '')
        add_sms_status = request.POST.get('add_sms_status', None)
        add_sms_conent = request.POST.get('add_sms_conent', None)
        # add_sms_contact  = request.POST.get('add_sms_contact', None)
        add_enable = request.POST.get('add_enable', None)
        add_remake = request.POST.get('add_remake', None)
        add_ins = request.POST.getlist('add_ins', None)
        add_sms_contact = request.POST.getlist('add_sms_contact', None)
        add_email_contact = request.POST.getlist('add_email_contact', None)
        add_task_type = request.POST.getlist('add_task_type', None)
        # 生成beat_crontabschedule的参数列表
        # arg_sqltext = cron_models.oratk_cron_point_info.objects.filter(name=add_task_point).first()
        # args_list.append(arg_sqltext.sqltext)
        # args_list.append(add_limit)
        # args_list.append(add_task_point)
        if add_enable == '是':
            add_enable = 1
        else:
            add_enable = 0
        if add_limit_status == '是':
            add_limit_status = 1
        else:
            add_limit_status = 0
        if add_sms_status == '是':
            add_sms_status = 1
        else:
            add_sms_status = 0
        ##kwargs字典
        kwargs_dict['task_name'] = add_name
        kwargs_dict['point_name'] = add_task_point
        kwargs_dict['limit_status'] = add_limit_status
        kwargs_dict['limit'] = add_limit
        kwargs_dict['limit_unit'] = add_limit_unit
        kwargs_dict['sms_status'] = add_sms_status
        kwargs_dict['sms_conent'] = add_sms_conent
        # kwargs_dict['sms_contact'] = add_sms_contact
        kwargs_dict['ins'] = add_ins
        kwargs_dict['sms_contact'] = add_sms_contact
        kwargs_dict['email_contact'] = add_email_contact
        task_kwargs = json.dumps(kwargs_dict)
        ##获取beat_crontabschedule中的id
        task_time = cron_models.oratk_cron_time_info.objects.filter(name=add_task_time).first()
        beat_models.PeriodicTask.objects.create(name=add_name, description=add_desc, task=add_task_info,
                                                kwargs=task_kwargs, crontab_id=task_time.type_id.id, enabled=add_enable)
        cron_models.oratk_cron_task.objects.create(name_id=add_name, task_info=add_task_info, task_point=add_task_point,
                                                   task_time=add_task_time,
                                                   limit=add_limit, remake=add_remake, limit_status=add_limit_status,
                                                   sms_status=add_sms_status, sms_conent=add_sms_conent,
                                                   sms_contact=add_sms_contact, email_contact=add_email_contact,
                                                   instance=add_ins, chtime=curr_time, task_type=add_task_type,
                                                   limit_unit=add_limit_unit)

        return redirect("/oratk_app/cron_task_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            ins_info = basic_models.instance_info.objects.exclude(status='locked')
            user_info = basic_models.user_info.objects.exclude(status='locked')
            task_info = cron_models.oratk_cron_task_info.objects.filter(type='sql类')
            task_time = cron_models.oratk_cron_time_info.objects.all()
            task_point = cron_models.oratk_cron_point_info.objects.filter(point_type='sql类')
            return render(request, 'oratk_app/cron_task_add.html',
                          {'task_info': task_info, 'task_time': task_time, 'task_point': task_point,
                           'ins_info': ins_info, 'user_info': user_info})
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_add_host(request):
    if request.method == 'POST':
        curr_time = time.strftime('%Y年%m月%d日 %H:%M:%S', time.localtime(time.time()))
        kwargs_dict = {}
        add_name = request.POST.get('add_name', None)
        add_desc = request.POST.get('add_desc', None)
        add_task_info = request.POST.get('add_tash_info', None)
        add_task_time = request.POST.get('add_task_time', None)
        add_task_point = request.POST.get('add_task_point', None)
        add_limit_status = request.POST.get('add_limit_status', '是')
        add_limit = request.POST.get('add_limit', '0')
        add_limit_unit = request.POST.get('add_limit_unit', '')
        add_sms_status = request.POST.get('add_sms_status', None)
        add_sms_conent = request.POST.get('add_sms_conent', None)
        add_enable = request.POST.get('add_enable', None)
        add_remake = request.POST.get('add_remake', None)
        add_ins = request.POST.getlist('add_ins', None)
        add_sms_contact = request.POST.getlist('add_sms_contact', None)
        add_email_contact = request.POST.getlist('add_email_contact', None)
        add_task_type = request.POST.get('add_task_type', None)
        print(add_ins)
        if add_enable == '是':
            add_enable = 1
        else:
            add_enable = 0
        if add_limit_status == '是':
            add_limit_status = 1
        else:
            add_limit_status = 0
        if add_sms_status == '是':
            add_sms_status = 1
        else:
            add_sms_status = 0
        ##kwargs字典
        kwargs_dict['task_name'] = add_name
        kwargs_dict['point_name'] = add_task_point
        kwargs_dict['limit_status'] = add_limit_status
        kwargs_dict['limit'] = add_limit
        kwargs_dict['limit_unit'] = add_limit_unit
        kwargs_dict['sms_status'] = add_sms_status
        kwargs_dict['sms_conent'] = add_sms_conent
        # kwargs_dict['sms_contact'] = add_sms_contact
        kwargs_dict['ins'] = add_ins
        kwargs_dict['sms_contact'] = add_sms_contact
        kwargs_dict['email_contact'] = add_email_contact
        task_kwargs = json.dumps(kwargs_dict)
        ##获取beat_crontabschedule中的id
        task_time = cron_models.oratk_cron_time_info.objects.filter(name=add_task_time).first()
        beat_models.PeriodicTask.objects.create(name=add_name, description=add_desc, task=add_task_info,
                                                kwargs=task_kwargs, crontab_id=task_time.type_id.id, enabled=add_enable)
        cron_models.oratk_cron_task.objects.create(name_id=add_name, task_info=add_task_info, task_point=add_task_point,
                                                   task_time=add_task_time,
                                                   limit=add_limit, remake=add_remake, limit_status=add_limit_status,
                                                   sms_status=add_sms_status, sms_conent=add_sms_conent,
                                                   sms_contact=add_sms_contact, email_contact=add_email_contact,
                                                   instance=add_ins, chtime=curr_time, task_type=add_task_type,
                                                   limit_unit=add_limit_unit)

        return redirect("/oratk_app/cron_task_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            ins_info = basic_models.instance_info.objects.exclude(status='locked')
            user_info = basic_models.user_info.objects.exclude(status='locked')
            host_info = basic_models.host_info.objects.exclude(status='locked')
            task_info = cron_models.oratk_cron_task_info.objects.filter(type='host类')
            task_time = cron_models.oratk_cron_time_info.objects.all()
            task_point = cron_models.oratk_cron_point_info.objects.filter(point_type='host类')
            return render(request, 'oratk_app/cron_task_add_host.html',
                          {'task_info': task_info, 'task_time': task_time, 'task_point': task_point,
                           'ins_info': ins_info, 'user_info': user_info, 'host_info': host_info})
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_edit(request):
    if request.method == 'POST':
        curr_time = time.strftime('%Y年%m月%d日 %H:%M:%S', time.localtime(time.time()))
        kwargs_dict = {}
        add_id = request.POST.get('add_nid', None)
        add_name = request.POST.get('add_name', None)
        add_desc = request.POST.get('add_desc', None)
        add_tash_info = request.POST.get('add_tash_info', None)
        add_task_time = request.POST.get('add_task_time', None)
        add_task_point = request.POST.get('add_task_point', None)
        add_limit_status = request.POST.get('add_limit_status', None)
        add_limit = request.POST.get('add_limit', None)
        add_limit_unit = request.POST.get('add_limit_unit', '')
        add_sms_status = request.POST.get('add_sms_status', None)
        add_sms_conent = request.POST.get('add_sms_conent', None)
        add_remake = request.POST.get('add_remake', None)
        add_ins = request.POST.getlist('add_ins', None)
        add_sms_contact = request.POST.getlist('add_sms_contact', None)
        add_email_contact = request.POST.getlist('add_email_contact', None)
        add_task_type = request.POST.get('add_task_type', None)
        # 生成beat_crontabschedule的参数列表
        arg_sqltext = cron_models.oratk_cron_point_info.objects.filter(name=add_task_point).first()
        ##kwargs字典
        kwargs_dict['task_name'] = add_name
        kwargs_dict['point_name'] = add_task_point
        kwargs_dict['limit_status'] = add_limit_status
        kwargs_dict['limit'] = add_limit
        kwargs_dict['limit_unit'] = add_limit_unit
        kwargs_dict['sms_status'] = add_sms_status
        kwargs_dict['sms_conent'] = add_sms_conent
        kwargs_dict['sms_contact'] = add_sms_contact
        kwargs_dict['email_contact'] = add_email_contact
        kwargs_dict['ins'] = add_ins
        task_kwargs = json.dumps(kwargs_dict)
        ##获取beat_crontabschedule中的id
        task_time = cron_models.oratk_cron_time_info.objects.filter(name=add_task_time).first()
        PeriodicTask_id = cron_models.oratk_cron_task.objects.filter(id=add_id).first()
        beat_models.PeriodicTask.objects.filter(id=PeriodicTask_id.name.id).update(name=add_name, description=add_desc,
                                                                                   task=add_tash_info,
                                                                                   kwargs=task_kwargs,
                                                                                   crontab_id=task_time.type_id.id)
        task_enable = beat_models.PeriodicTask.objects.get(id=PeriodicTask_id.name.id)
        task_enable.save()
        cron_models.oratk_cron_task.objects.filter(id=add_id).update(name_id=add_name, task_info=add_tash_info,
                                                                     task_point=add_task_point, task_time=add_task_time,
                                                                     limit=add_limit, remake=add_remake,
                                                                     limit_status=add_limit_status,
                                                                     sms_status=add_sms_status,
                                                                     sms_conent=add_sms_conent,
                                                                     sms_contact=add_sms_contact,
                                                                     email_contact=add_email_contact, instance=add_ins,
                                                                     chtime=curr_time,
                                                                     task_type=add_task_type, limit_unit=add_limit_unit)

        return redirect("/oratk_app/cron_task_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            nid_task_info = cron_models.oratk_cron_task.objects.filter(id=nid).values('task_info').first()
            # 生成beat_crontabschedule的参数列表
            host_info = basic_models.host_info.objects.exclude(status='locked')
            user_info = basic_models.user_info.objects.exclude(status='locked')
            ins_info = basic_models.instance_info.objects.exclude(status='locked')
            task_info = cron_models.oratk_cron_task_info.objects.all()
            task_time = cron_models.oratk_cron_time_info.objects.all()
            task_point = cron_models.oratk_cron_point_info.objects.all()
            cron_task_result = cron_models.oratk_cron_task.objects.filter(id=nid).values("id", "name",
                                                                                         "name__description",
                                                                                         "task_time", "task_point",
                                                                                         "task_info", "limit_status",
                                                                                         "limit", "limit_unit",
                                                                                         "sms_status", "sms_conent",
                                                                                         "sms_contact", "email_contact",
                                                                                         "name__enabled", "remake",
                                                                                         "instance",
                                                                                         "task_type").first()
            print(nid_task_info['task_info'])
            if nid_task_info['task_info'] == 'cron.tasks.task_kwargs':
                return render(request, 'oratk_app/cron_task_edit.html', {'cron_task_result': cron_task_result,
                                                                         'task_info': task_info, 'task_time': task_time,
                                                                         'task_point': task_point, 'ins_info': ins_info,
                                                                         'user_info': user_info})
            else:
                return render(request, 'oratk_app/cron_task_edit_host.html', {'cron_task_result': cron_task_result,
                                                                              'task_info': task_info,
                                                                              'task_time': task_time,
                                                                              'task_point': task_point,
                                                                              'ins_info': ins_info,
                                                                              'user_info': user_info,
                                                                              'host_info': host_info})
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_delete(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            task_name = cron_models.oratk_cron_task.objects.filter(id=nid).first()
            beat_models.PeriodicTask.objects.filter(name=task_name.name_id).delete()
            return redirect("/oratk_app/cron_task_mgr")
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_lock(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            task_name = cron_models.oratk_cron_task.objects.filter(id=nid).first()
            beat_models.PeriodicTask.objects.filter(name=task_name.name_id).update(enabled=0)
            task_enable = beat_models.PeriodicTask.objects.get(name=task_name.name_id)
            task_enable.enable = 0
            task_enable.save()
            return redirect("/oratk_app/cron_task_mgr")
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_enable(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            task_name = cron_models.oratk_cron_task.objects.filter(id=nid).first()
            beat_models.PeriodicTask.objects.filter(name=task_name.name_id).update(enabled=1)
            task_enable = beat_models.PeriodicTask.objects.filter(name=task_name.name_id).first()
            task_enable.enable = 1
            print(task_enable.id, task_enable.enable)
            task_enable.save()
            return redirect("/oratk_app/cron_task_mgr")
        else:
            return redirect('/basic_app/login.html')


def cron_task_mgr_more(request):
    if request.method == 'POST':
        return redirect("/oratk_app/cron_point_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            more_result = cron_models.oratk_cron_point_info.objects.get(id=nid)
            return render(request, 'oratk_app/cron_point_more.html', {'more_result': more_result})
        else:
            return redirect('/basic_app/login.html')


###计划任务-定义时间
def cron_time_mgr(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            ##当用户搜索时：
            if user_search != 'all':
                cron_point_info_result = cron_models.oratk_cron_time_info.objects.filter(Q(name__contains=user_search)
                                                                                         | Q(
                    type__contains=user_search)).values("id", "name", "type", "type_id__id", "type_id__minute"
                                                        , "type_id__hour", "type_id__day_of_week"
                                                        , "type_id__day_of_month", "type_id__month_of_year",
                                                        "type_id__timezone")
                v_paginator(cron_point_info_result, currentpage_num, line_count_set)
            # 当用户没有搜索时：
            else:
                user_search = ''
                # cron_point_info_result = beat_models.CrontabSchedule.objects.all().order_by('-id')
                cron_point_info_result = cron_models.oratk_cron_time_info.objects.all().order_by('id').values("id",
                                                                                                              "name",
                                                                                                              "type",
                                                                                                              "type_id__id",
                                                                                                              "type_id__minute"
                                                                                                              ,
                                                                                                              "type_id__hour",
                                                                                                              "type_id__day_of_week"
                                                                                                              ,
                                                                                                              "type_id__day_of_month",
                                                                                                              "type_id__month_of_year",
                                                                                                              "type_id__timezone")
                v_paginator(cron_point_info_result, currentpage_num, line_count_set)
            return render(request, 'oratk_app/cron_time_mgr.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')


def cron_time_mgr_add(request):
    if request.method == 'POST':
        add_name = request.POST.get('add_name', None)
        add_minute = request.POST.get('add_minute', None)
        add_hour = request.POST.get('add_hour', None)
        add_day_of_week = request.POST.get('add_day_of_week', None)
        add_day_of_month = request.POST.get('add_day_of_month', None)
        add_month_of_year = request.POST.get('add_month_of_year', None)
        add_timezone = request.POST.get('add_timezone', None)
        add_type = request.POST.get('add_type', None)
        beat_models.CrontabSchedule.objects.create(minute=add_minute, hour=add_hour, day_of_week=add_day_of_week,
                                                   day_of_month=add_day_of_month,
                                                   month_of_year=add_month_of_year, timezone=add_timezone)
        CrontabSchedule_id = beat_models.CrontabSchedule.objects.all().order_by('-id').first()
        cron_models.oratk_cron_time_info.objects.create(name=add_name, type=add_type, type_id_id=CrontabSchedule_id.id)

        return redirect("/oratk_app/cron_time_mgr")
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            return render(request, 'oratk_app/cron_time_add.html')
        else:
            return redirect('/basic_app/login.html')


def cron_time_mgr_delete(request):
    if request.method == 'POST':
        pass
    if request.method == 'GET':
        if request.session.get('is_login', None):
            ##关闭浏览器删除session
            request.session.set_expiry(0)
            nid = request.GET.get('nid')
            time_info = cron_models.oratk_cron_time_info.objects.filter(type_id=nid).values('name').first()['name']
            try:
                task_time_info = \
                    cron_models.oratk_cron_task.objects.filter(task_time=time_info).values('task_time').first()[
                        'task_time']
                if task_time_info:
                    data = {'result': '不能删除该监控时间', 'msg': '该监控时间已被使用'}
                    return JsonResponse(data, json_dumps_params={'ensure_ascii': False})
                else:
                    # print(task_point_info)
                    beat_models.CrontabSchedule.objects.filter(id=nid).delete()
                    return redirect("/oratk_app/cron_time_mgr")
            except:
                beat_models.CrontabSchedule.objects.filter(id=nid).delete()
                return redirect("/oratk_app/cron_time_mgr")
        else:
            return redirect('/basic_app/login.html')


###计划任务执行情况
def cron_mon_exec_info(request):
    if request.method == 'GET':
        if request.session.get('is_login', None):
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            cron_task_exec_info = cron_models.oratk_cron_exec_info.objects.all().order_by('-id')
            v_paginator(cron_task_exec_info, currentpage_num, line_count_set)
            return render(request, 'oratk_app/cron_task_exec_info.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')
    if request.method == 'POST':
        pass


###短信发送记录
def cron_mon_sms_send(request):
    if request.method == 'GET':
        if request.session.get('is_login', None):
            request.session.set_expiry(0)
            currentpage_num = int(request.GET.get('page', '1'))
            line_count_set = int(request.GET.get('line_count', 5))
            user_search = request.GET.get('user_search', 'all')
            cron_sms_exec_info = cron_models.oratk_cron_sms_send.objects.all().order_by('-id')
            v_paginator(cron_sms_exec_info, currentpage_num, line_count_set)
            return render(request, 'oratk_app/cron_sms_exec_info.html',
                          {'current_page': current_page, 'paginator': paginator, 'line_count': line_count_set,
                           'page_range': page_range, 'last_page': last_page, 'current_page_num': currentpage_num
                              , 'search': user_search})
        else:
            return redirect('/basic_app/login.html')
    if request.method == 'POST':
        pass
